T-SQL Syntax: INSERT Statement

The INSERT statement is used to add one or more rows to a table in a SQL Server database. It can be used to insert a single row, multiple rows, or the results of a query into a table.

Basic Syntax

The most common syntax for the INSERT statement is:


INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Or, if you are providing values for all columns in their defined order:


INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Syntax with a SELECT Statement

You can insert the results of a SELECT query into another table:


INSERT INTO table_name (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;

Syntax with INSERT FROM OPENJSON

For inserting data from a JSON string:


INSERT INTO table_name (column1, column2)
SELECT JSON_VALUE(value, '$.property1'), JSON_VALUE(value, '$.property2')
FROM OPENJSON(@json_string);

Parameters

table_name: The name of the table to which you want to add rows.
column1, column2, ...: The names of the columns you are inserting data into. If omitted, values must be provided for all columns in the table's defined order.
value1, value2, ...: The data values to be inserted for each column. These values must correspond to the order and data type of the columns specified.
SELECT ... FROM ... WHERE ...: A SELECT statement that retrieves data from one or more tables to be inserted into the target table.
OPENJSON(@json_string): A table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns.
JSON_VALUE(value, '$.property'): Extracts a scalar value from a JSON string.

Examples

Example 1: Inserting a Single Row

Insert a new record into the Customers table.


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

Example 2: Inserting Multiple Rows

Insert several new records into the Products table.


INSERT INTO Products (ProductID, ProductName, Price)
VALUES
    (78, 'Chai', 18.00),
    (79, 'Chang', 19.00),
    (80, 'Aniseed Syrup', 10.00);

Example 3: Inserting Data from Another Table

Copy data from an old customer table to a new one.


INSERT INTO NewCustomers (CustomerID, CustomerName)
SELECT OldID, Name
FROM OldCustomers
WHERE City = 'London';

Example 4: Inserting with Column List Omitted

If you provide values for all columns in the correct order.


-- Assuming 'Orders' table has columns: OrderID, CustomerID, OrderDate
INSERT INTO Orders
VALUES (10001, 5, '2023-10-27');

Key Considerations

Data Types: Ensure that the data types of the values you provide match the data types of the columns you are inserting into.
Constraints: INSERT operations are subject to table constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL. Violations will result in an error.
Identity Columns: If a table has an IDENTITY column, you do not typically provide a value for it; SQL Server automatically generates it. You can use SET IDENTITY_INSERT table_name ON; to manually insert values into an identity column, but this should be done with caution.
NULL Values: You can insert NULL values into columns that allow them.
Default Values: If a column has a default value defined and you do not provide a value for it in the INSERT statement, the default value will be used.