Adding rows with INSERT

INSERT adds rows to a table. Name the table, list the columns you are filling, and give the values in the same order:

1INSERT INTO dbo.Categories (CategoryId, Name)2VALUES (8, N'Garden');3 4SELECT * FROM dbo.Categories WHERE CategoryId >= 7;

CategoryId  Name
----------  --------
         7  Plumbing
         8  Garden

Try the statements below, including the ones that fail. Each runs on a fresh BuildMart, and every message is SQL Server’s own:

1INSERT INTO dbo.Categories (CategoryId, Name)2VALUES (8, N'Garden');3 4SELECT * FROM dbo.Categories WHERE CategoryId >= 6;

Columns you leave out

A column missing from the list gets its DEFAULT value if it has one, an IDENTITY number if it is an IDENTITY column, and otherwise NULL. If it is NOT NULL with no default, the whole INSERT fails. Products needs every column except ProductId:

1INSERT INTO dbo.Products (Name, CategoryId, Price, Stock, Unit, AddedOn)2VALUES (N'Spirit level 600 mm', 1, 15.00, 20, N'each', '2024-07-01'),3       (N'Mortar mix 20 kg',    2,  6.40, 90, N'bag',  '2024-07-01');4 5SELECT ProductId, Name, Price, Stock FROM dbo.Products WHERE ProductId > 11;

ProductId  Name                 Price  Stock
---------  -------------------  -----  -----
       12  Safety goggles        6.80      0
       13  Spirit level 600 mm  15.00     20
       14  Mortar mix 20 kg      6.40     90

INSERT … SELECT: copy rows from a query

Instead of VALUES, an INSERT can take the result of a SELECT. Every row the query returns is inserted. Here BuildMart keeps a list of products to reorder:

1CREATE TABLE dbo.Reorders (2    ProductId int          NOT NULL PRIMARY KEY,3    Name      nvarchar(60) NOT NULL,4    Quantity  int          NOT NULL5);6 7INSERT INTO dbo.Reorders (ProductId, Name, Quantity)8SELECT ProductId, Name, 1009FROM dbo.Products10WHERE Stock < 20;11 12SELECT * FROM dbo.Reorders;

ProductId  Name              Quantity
---------  ----------------  --------
        2  Cordless drill         100
        9  Wall plugs (100)       100
       12  Safety goggles         100

SELECT … INTO: a new table from a query

SELECT … INTO NewTable creates the table and fills it in one go, copying column names and types from the query. It is perfect for a quick backup copy before you change something. It does not copy keys, constraints or indexes.

1SELECT ProductId, Name, Price2INTO dbo.PriceBackup3FROM dbo.Products4WHERE CategoryId = 5;5 6SELECT * FROM dbo.PriceBackup;

ProductId  Name                Price
---------  ------------------  -----
       10  Masonry paint 10 L  54.00
       11  Paint roller set    12.50

OUTPUT: see what was inserted

The OUTPUT clause returns the rows an INSERT just added, including values SQL Server filled in itself, such as IDENTITY numbers. It goes between the column list and VALUES:

1INSERT INTO dbo.Products (Name, CategoryId, Price, Stock, Unit, AddedOn)2OUTPUT inserted.ProductId, inserted.Name3VALUES (N'Garden spade', 1, 24.00, 15, N'each', '2024-07-01'),4       (N'Leaf rake',    1, 16.50, 10, N'each', '2024-07-01');

ProductId  Name
---------  ------------
       13  Garden spade
       14  Leaf rake

1INSERT INTO dbo.Categories (CategoryId, Name)2VALUES (8, N'Garden');3 4INSERT INTO dbo.Products (Name, CategoryId, Price, Stock, Unit, AddedOn)5VALUES (N'Garden hose 30 m', 8, 27.99, 18, N'each', '2024-07-01'),6       (N'Compost 40 L',     8,  5.49, 60, N'bag',  '2024-07-01');7 8SELECT ProductId, Name, Price, Stock, Unit9FROM dbo.Products10WHERE CategoryId = 8;

ProductId  Name              Price  Stock  Unit
---------  ----------------  -----  -----  ----
       13  Garden hose 30 m  27.99     18  each
       14  Compost 40 L       5.49     60  bag