INSERT Statement (Transact-SQL)

This topic describes the syntax for the INSERT statement in Transact-SQL (T-SQL).

Purpose

The INSERT statement is used to add one or more rows to a table in a SQL Server database.

Syntax

1. Inserting a single row with explicit column list

INSERT [ TOP ( expression ) [ PERCENT ] ] [ INTO ] { object_name } [ ( column_list ) ] VALUES ( value_list ) [ ; ]

2. Inserting a single row with values inferred from another SELECT statement

INSERT [ TOP ( expression ) [ PERCENT ] ] [ INTO ] { object_name } [ ( column_list ) ] { <;SELECT statement>; } [ ; ]

3. Inserting multiple rows using a SELECT statement

INSERT [ INTO ] { object_name } [ ( column_list ) ] <;SELECT statement>; [ ; ]

4. Inserting data using INSERT...EXEC statement

INSERT ( column_list ) <;EXECUTE | EXEC> { stored_procedure_name [ arguments ] } [ ; ]

Parameters

Parameter	Description
`TOP ( expression ) [ PERCENT ]`	Optional. Specifies that only a certain number or percentage of rows satisfying the query are inserted. This is typically used with `SELECT` statements to limit the number of rows being inserted.
`INTO`	Optional keyword.
`object_name`	The name of the table or view into which rows are inserted.
`column_list`	A comma-separated list of columns in the specified table or view into which values are inserted. If omitted, the values in the `value_list` must be in the same order as the columns in the table definition.
`VALUES ( value_list )`	Specifies the values to be inserted. The number and data types of the values must match the number and data types of the specified columns.
`value_list`	A comma-separated list of literal values, variables, or expressions to be inserted into the corresponding columns.
`SELECT statement`	A standard `SELECT` statement that returns the rows to be inserted. The number and data types of the columns in the `SELECT` list must match the number and data types of the columns in the `INSERT INTO` clause or the table definition.
`EXECUTE \| EXEC`	Executes a stored procedure or extended stored procedure.
`stored_procedure_name`	The name of the stored procedure to execute.
`arguments`	Optional. One or more arguments passed to the stored procedure.

Examples

Example 1: Inserting a single row

INSERT INTO Customers (CustomerID, CompanyName, ContactName)
VALUES (101, 'New Corp', 'Jane Doe');

Example 2: Inserting a row without specifying columns (order must match table)

-- Assuming the table has columns: CustomerID, CompanyName, ContactName, City
INSERT INTO Customers
VALUES (102, 'Tech Solutions', 'John Smith', 'New York');

Example 3: Inserting multiple rows from another table

INSERT INTO ArchivedCustomers (CustomerID, CompanyName)
SELECT CustomerID, CompanyName
FROM Customers
WHERE LastPurchaseDate < '2023-01-01';

Example 4: Inserting data from a stored procedure

INSERT INTO ProductSales (ProductID, SaleAmount)
EXEC GetRecentSales;