INSERT (Transact‑SQL)
The INSERT statement adds new rows to a table or view in a SQL Server database.
Syntax
INSERT [TOP (expression) [PERCENT]]
[INTO] target_table [(column_list)]
{ VALUES (value_list) [ ,...n ] |
derived_table |
execute_statement }
[OUTPUT clause]
[;]Parameters
- TOP (expression) – Limits the number of rows inserted.
- INTO – Optional keyword.
- target_table – The table or view to receive the new rows.
- column_list – List of columns to receive values; omitted means all columns.
- VALUES – Specifies literal values.
- derived_table – A SELECT statement that provides rows.
- execute_statement – Executes a stored procedure that returns rows.
- OUTPUT clause – Returns information from each inserted row.
Examples
Basic INSERT
INSERT INTO dbo.Products (ProductName, Price)
VALUES ('Widget', 19.99);INSERT with SELECT
INSERT INTO dbo.ArchiveOrders (OrderID, CustomerID, OrderDate)
SELECT OrderID, CustomerID, OrderDate
FROM dbo.Orders
WHERE OrderDate < '2024-01-01';INSERT with OUTPUT
INSERT INTO dbo.Employees (FirstName, LastName)
OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName
VALUES ('Jane', 'Doe');Notes
- Column values must match the data type of the target column.
- If
IDENTITY_INSERTis OFF, you cannot insert explicit values for identity columns. - Using
TOPwithINSERTis only valid when inserting from a SELECT. - The
OUTPUTclause can capture inserted rows for further processing.