INSERT Statement (Transact-SQL)

INSERT (Transact-SQL)

Specifies the insert mode for a cursor.

Syntax

Transact-SQL syntax conventions

INSERT { <object> |<table_ref> }
    [ ( { <column_list> } ) ]
    { <values_rows> | <select_statement> | <derived_table> | <execute_clause> }
    [ <output_clause> ]
    [;]

<object> ::=
    [ database_name. ] [ schema_name. ] object_name

<table_ref> ::=
    FROM <table_source> [ ,...n ]

<values_rows> ::=
    VALUES
    ( { DEFAULT | NULL | expression } [ ,...n ] )
    [ ,...n ]

<select_statement> ::=
    SELECT [ ALL | DISTINCT ] <select_list>
    [ INTO temp_table | table_variable ]
    FROM <table_source> [ ,...n ]
    [ WHERE <search_condition> ]
    [ GROUP BY <group_by_expression> [ ,...n ] ]
    [ HAVING <search_condition> ]
    [ ORDER BY <order_by_expression> [ ,...n ] ]
    [ OPTION ( <query_option> [ ,...n ] ) ]

<derived_table> ::=
    ( <select_statement> ) AS <table_alias> ( <column_alias_list> )

<execute_clause> ::=
    EXECUTE [ @return_status = ] { <procedure_name> | @procedure_name_var }
    [ <procedure_param> [ ...n ] ]
    [ <output_clause> ]

<output_clause> ::=
    OUTPUT
        { $action | inserted.* | deleted.* | inserted.column_name | deleted.column_name } [ ,...n ]
    [ INTO { <object> | @table_variable }
        [ ( { column_list } ) ]
    ]

Arguments

`<object> | <table_ref>`

Specifies the table or view to insert data into.

database_name.schema_name.object_name: The fully qualified name of the table or view.
FROM <table_source>: Used for inserting from another table source.

`<column_list>`

A comma-separated list of columns in the table that will receive values.

`<values_rows>`

Specifies the literal values to be inserted. Each set of parentheses represents a single row.

DEFAULT: Inserts the default value defined for the column.
NULL: Inserts a null value.
expression: A constant, variable, or expression that evaluates to a value for the column.

`<select_statement>`

A SELECT statement that retrieves rows from one or more tables to be inserted.

`<derived_table>`

A subquery that returns a result set with a specified alias and column names.

`<execute_clause>`

Executes a stored procedure that returns a result set. The result set is then inserted into the target table.

`<output_clause>`

Returns information from, or the results of expressions that involve, rows affected by an INSERT statement. The output can be directed to another table or table variable.

Permissions

Requires INSERT permission on the target table.

Examples

Basic INSERT Statement

Inserts a single row into the Person.Person table.

INSERT INTO Person.Person (FirstName, LastName, EmailPromotion)
VALUES ('Sven', 'Mortensen', 2);

INSERT Statement with SELECT

Inserts rows from the Sales.SalesPersonQuotaHistory table into a new table.

INSERT INTO Sales.SalesPersonQuotaHistory (SalesPersonID, QuotaDate, SalesQuota, rowguid, ModifiedDate)
SELECT SalesPersonID, Date_value, Quota, NEWID(), GETDATE()
FROM Sales.SalesPerson
WHERE TerritoryID = 5;

INSERT Statement with OUTPUT Clause

Inserts data and captures the inserted rows into a table variable.

DECLARE @OutputTable TABLE (
    ProductID INT,
    ProductName VARCHAR(50),
    NewPrice DECIMAL(10, 2)
);

INSERT INTO Production.Product (Name, ProductNumber, Color, StandardCost, ListPrice, SellStartDate)
OUTPUT inserted.ProductID, inserted.Name, inserted.ListPrice
INTO @OutputTable (ProductID, ProductName, NewPrice)
VALUES
('Mountain-200 Black, 46', 'BK-M29I-46', 'Black', 58.39, 78.23, GETDATE());

SELECT ProductID, ProductName, NewPrice FROM @OutputTable;

Microsoft Docs