Inserting Data

Add rows to a table with INSERT INTO, name the columns you fill, let identity columns number new rows, and respect the table's keys.

Apprentice · T-SQL · SQL · Updated

After this lesson you can

  • Add rows with INSERT INTO, naming the columns you fill
  • Explain how identity columns, required columns and foreign keys affect an insert

Key takeaways

  • INSERT INTO names the columns you fill, then VALUES gives their values in the same order. Text and dates go in single quotes; numbers don't.
  • Identity columns such as ProductId number themselves. Read SCOPE_IDENTITY() straight after the insert to learn the new id; never guess it.
  • A column you leave out gets its default or NULL, so leaving out a required column such as ProductName makes the insert fail.
  • A primary key can't be repeated, and a foreign key must point to a row that exists, so insert the row it points to first.
  • A single quote inside text is written twice: 'Joe''s Couriers' stores Joe's Couriers.

SELECT reads data. To add new rows, you use INSERT. Changing data is serious work, so this chapter shows each statement together with the habits that keep you out of trouble.

Try every example in the Northwind database. If you make a mess, run Northwind.sql again to start fresh.

The shape of an INSERT

INSERT INTO <table> (<column1>, <column2>, ...)
VALUES (<value1>, <value2>, ...);

List the columns you're filling, then the values for them, in the same order. Text and dates go in single quotes; numbers don't.

INSERT INTO Categories (CategoryName, Description)
VALUES ('Sporting Goods', 'Equipment for team sports, camping and river rafting');

SSMS answers in the Messages tab:

(1 row affected)

Check it with a SELECT:

SELECT CategoryId, CategoryName
FROM Categories
WHERE CategoryName = 'Sporting Goods';

Identity columns number themselves

You didn't give the new category a CategoryId. You don't have to, and you can't: CategoryId is an identity column, and SQL Server gives each new row the next number itself. The same is true of ProductId, OrderId and EmployeeId in Northwind.

To find out which number a new row got, read SCOPE_IDENTITY() straight after the insert. Store it in a variable, a named value that lasts while the script runs:

DECLARE @NewProductId int;

INSERT INTO Products (ProductName, CategoryId, UnitPrice, UnitsInStock)
VALUES ('Hot Chocolate', 1, 5.99, 50);

SET @NewProductId = SCOPE_IDENTITY();
SELECT @NewProductId AS NewProductId;

On a fresh Northwind the new product is number 78, the next after the 77 that exist. The number can be higher on your computer: an identity number is used up even when an insert is undone, so gaps are normal. Never guess an id; ask for it.

T-SQL variables start with @. DECLARE creates one with a type, and SET gives it a value.

Not every column needs a value

In the example above, SupplierId, QuantityPerUnit and others were left out. A column you don't fill gets its default (UnitsOnOrder defaults to 0) or, if it has none, NULL. That only works for columns that allow it. Leave out a required column, and the insert fails:

INSERT INTO Products (CategoryId, UnitPrice)
VALUES (1, 5.99);

Cannot insert the value NULL into column 'ProductName', table 'Northwind.dbo.Products'; column does not allow nulls. INSERT fails.

Keys protect the data

A table's primary key identifies each row, so it must be unique. Customers have a five-letter CustomerId that you choose yourself, and you can't reuse one:

INSERT INTO Customers (CustomerId, CompanyName)
VALUES ('ALFKI', 'Another Alfreds');

Violation of PRIMARY KEY constraint 'PK_Customers'. Cannot insert duplicate key in object 'dbo.Customers'. The duplicate key value is (ALFKI).

A foreign key links a row to a row in another table. Every product's CategoryId must be a category that exists. There's no category 99, so:

INSERT INTO Products (ProductName, CategoryId, UnitPrice)
VALUES ('Hot Chocolate', 99, 5.99);

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Products_Categories". The conflict occurred in database "Northwind", table "dbo.Categories", column 'CategoryId'.

These errors are the database protecting you: it won't store a duplicate customer, or a product in a category that doesn't exist. Insert the row a foreign key points to first, then the rows that point to it.

Several rows at once

One INSERT can add several rows: separate each row's values with a comma.

INSERT INTO Shippers (CompanyName, Phone)
VALUES ('Rapid Delivery', '(503) 555-0110')
    , ('Mountain Freight', '(503) 555-0120');

(2 rows affected)

A quote inside text

Text goes in single quotes, so a single quote inside the text is written twice:

INSERT INTO Shippers (CompanyName, Phone)
VALUES ('Joe''s Couriers', '(503) 555-0130');

That stores Joe's Couriers.

Check yourself

2 questions to try on the lesson page.

So what

An INSERT names its columns, lets identity columns number themselves, and works with the table's keys, not against them. Read each error carefully: it's the database telling you exactly which rule your row broke.

Key terms

Cheat sheets