INSERT Statement - Transact-SQL

INSERT (Transact-SQL)

Inserts new rows of data into a table or a view.

Syntax


INSERT [ { TOP ( expression [ PERCENT ] ) } 
    [ WITH (  [ , ...n ] ) ] 
    { <object> | <table_hint_plural> } 
    [ ( { column_name [ , ...n ] } ) ] 
    { <values_insert_statement> 
    | <SELECT_statement> 
    | <bulk_insert_statement> 
    | <merge_statement> 
    } 
    [ ; ] 
<object> ::= { <database_name>.<schema_name>.<table_or_view_name> 
    | <schema_name>.<table_or_view_name> 
    | <table_or_view_name> 
    } 
<table_hint_plural> ::= WITH ( <table_hint> [ , ...n ] ) 
<table_hint> ::= { { INDEX index_=\{  | <index_name> \} } 
    | { <index_option> [ , ...n ] } 
    | { FORCESEEK [ ( <seek_predicate> ) ] } 
    | { HOLDLOCK | ROWLOCK | TABLOCK | PAGLOCK | ... } 
    } 
<insert_option> ::= { ( CHECK_CONSTRAINTS | DISABLE_IDENTITY_INSERT | ... ) }
<values_insert_statement> ::= VALUES ( insert_<expression> [ , ...n ] ) 
<select_statement> ::= <query_expression> 
<bulk_insert_statement> ::= BULK INSERT ...
<merge_statement> ::= MERGE ...

Parameters

TOP ( expression [ PERCENT ] ): Optionally specifies the number or percentage of rows to insert.

WITH ( <insert_option> [ , ...n ] ): Specifies insertion options.

<object>: The target table or view where rows will be inserted.

( { column_name [ , ...n ] } ): Specifies the columns to insert data into. If omitted, all columns must be provided in the order they appear in the table definition.

<values_insert_statement>: Inserts one or more rows with specified values.

<SELECT_statement>: Inserts rows returned by a SELECT query.

<bulk_insert_statement>: Used for fast bulk loading of data.

<merge_statement>: Performs conditional inserts, updates, or deletes based on a match with a source table.

Examples

Basic INSERT with VALUES

This example inserts a single row into the Production.Product table.


INSERT INTO Production.Product (ProductID, Name, ProductNumber, Color, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice, SellStartDate)
VALUES (999, 'New Bike', 'NB-001', 'Red', 100, 50, 10.50, 25.00, GETDATE());

INSERT with SELECT

This example inserts rows from another table into the Sales.SpecialOfferProduct table.


INSERT INTO Sales.SpecialOfferProduct (SpecialOfferID, ProductID, UnitPrice, UnitPriceDiscount, Rowguid, ModifiedDate)
SELECT 1, ProductID, 10.00, 0.10, NEWID(), GETDATE()
FROM Production.Product
WHERE Color = 'Blue';

INSERT with IDENTITY_INSERT

This example inserts a row into a table with an identity column, specifying the identity value.


SET IDENTITY_INSERT dbo.MyTable ON;

INSERT INTO dbo.MyTable (ID, Data)
VALUES (101, 'Some Data');

SET IDENTITY_INSERT dbo.MyTable OFF;

Note: When inserting into a table with an IDENTITY column, you typically do not specify the identity column unless you explicitly enable IDENTITY_INSERT for the table.

Remarks

The INSERT statement is used to add new records to a table.
You can insert a single row, multiple rows, or rows derived from a query.
Ensure that the data types and constraints of the inserted values match the target columns.
For large data imports, consider using the BULK INSERT statement or the SQL Server Import and Export Wizard for better performance.
When inserting into a table with triggers, the triggers will be executed.

Microsoft Docs