INSERT (Transact-SQL)

  • Article
  • 35 minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics yesAnalytics Platform Organization (PDW)

Adds one or more rows to a table or a view in SQL Server. For examples, see Examples.

Topic link icon Transact-SQL Syntax Conventions

Syntax

              -- Syntax for SQL Server and Azure SQL Database    [ WITH <common_table_expression> [ ,...north ] ]   INSERT    {           [ Pinnacle ( expression ) [ PERCENT ] ]            [ INTO ]            { <object> | rowset_function_limited              [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]           }       {           [ ( column_list ) ]            [ <OUTPUT Clause> ]           { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ]            | derived_table            | execute_statement           | <dml_table_source>           | DEFAULT VALUES            }       }   }   [;]      <object> ::=   {        [ server_name . database_name . schema_name .          | database_name .[ schema_name ] .          | schema_name .        ]     table_or_view_name   }      <dml_table_source> ::=       SELECT <select_list>       FROM ( <dml_statement_with_output_clause> )          [As] table_alias [ ( column_alias [ ,...n ] ) ]       [ WHERE <search_condition> ]           [ Option ( <query_hint> [ ,...due north ] ) ]                          
              -- External tool only syntax    INSERT    {       [Majority]       { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }       ( <column_definition> )       [ WITH (           [ [ , ] CHECK_CONSTRAINTS ]           [ [ , ] FIRE_TRIGGERS ]           [ [ , ] KEEP_NULLS ]           [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]           [ [ , ] ROWS_PER_BATCH = rows_per_batch ]           [ [ , ] ORDER ( { cavalcade [ ASC | DESC ] } [ ,...n ] ) ]           [ [ , ] TABLOCK ]       ) ]   }      [; ] <column_definition> ::=    column_name <data_type>       [ COLLATE collation_name ]       [ Aught | NOT NULL ]      <data blazon> ::=    [ type_schema_name . ] type_name        [ ( precision [ , scale ] | max ]                          
              -- Syntax for Azure Synapse Analytics and Parallel Information Warehouse    INSERT [INTO] { database_name.schema_name.table_name | schema_name.table_name | table_name }     [ ( column_name [ ,...n ] ) ]       {          VALUES ( { Zero | expression } )         | SELECT <select_criteria>       }       [ Choice ( <query_option> [ ,...n ] ) ]   [;]

Arguments

WITH <common_table_expression>
Specifies the temporary named result set, as well known as common table expression, divers within the scope of the INSERT statement. The result set is derived from a SELECT statement. For more information, see WITH common_table_expression (Transact-SQL).

Top (expression) [ Pct ]
Specifies the number or percent of random rows that will be inserted. expression can be either a number or a percent of the rows. For more than information, run into Acme (Transact-SQL).

INTO
Is an optional keyword that tin be used betwixt INSERT and the target table.

server_name
Applies to: SQL Server 2008 and afterwards.

Is the name of the linked server on which the table or view is located. server_name can be specified as a linked server name, or by using the OPENDATASOURCE role.

When server_name is specified as a linked server, database_name and schema_name are required. When server_name is specified with OPENDATASOURCE, database_name and schema_name may not use to all data sources and is subject field to the capabilities of the OLE DB provider that accesses the remote object.

database_name
Applies to: SQL Server 2008 and later.

Is the name of the database.

schema_name
Is the name of the schema to which the table or view belongs.

table_or view_name
Is the proper name of the table or view that is to receive the data.

A table variable, within its telescopic, can be used as a table source in an INSERT argument.

The view referenced by table_or_view_name must be updatable and reference exactly ane base table in the FROM clause of the view. For case, an INSERT into a multi-table view must utilize a column_list that references only columns from one base of operations table. For more information about updatable views, see CREATE VIEW (Transact-SQL).

rowset_function_limited
Applies to: SQL Server 2008 and later.

Is either the OPENQUERY or OPENROWSET function. Use of these functions is subject area to the capabilities of the OLE DB provider that accesses the remote object.

WITH ( <table_hint_limited> [... n ] )
Specifies i or more than tabular array hints that are allowed for a target table. The WITH keyword and the parentheses are required.

READPAST, NOLOCK, and READUNCOMMITTED are not allowed. For more information about table hints, see Table Hints (Transact-SQL).

Important

The power to specify the HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD, or UPDLOCK hints on tables that are targets of INSERT statements will be removed in a future version of SQL Server. These hints do not bear on the performance of INSERT statements. Avoid using them in new development work, and plan to change applications that currently utilise them.

Specifying the TABLOCK hint on a table that is the target of an INSERT statement has the aforementioned result as specifying the TABLOCKX hint. An exclusive lock is taken on the table.

(column_list)
Is a listing of one or more than columns in which to insert data. column_list must exist enclosed in parentheses and delimited past commas.

If a column is non in column_list, the Database Engine must be able to provide a value based on the definition of the cavalcade; otherwise, the row cannot exist loaded. The Database Engine automatically provides a value for the column if the cavalcade:

  • Has an IDENTITY belongings. The side by side incremental identity value is used.

  • Has a default. The default value for the cavalcade is used.

  • Has a timestamp data blazon. The current timestamp value is used.

  • Is nullable. A nada value is used.

  • Is a computed column. The calculated value is used.

column_list must be used when explicit values are inserted into an identity cavalcade, and the Ready IDENTITY_INSERT option must exist ON for the table.

OUTPUT Clause
Returns inserted rows as part of the insert operation. The results can exist returned to the processing application or inserted into a table or table variable for further processing.

The OUTPUT clause is non supported in DML statements that reference local partitioned views, distributed partitioned views, or remote tables, or INSERT statements that incorporate an execute_statement. The OUTPUT INTO clause is not supported in INSERT statements that contain a <dml_table_source> clause. For more than information nearly the arguments and behavior of this clause, meet OUTPUT Clause (Transact-SQL).

VALUES
Introduces the list or lists of data values to be inserted. There must be one information value for each cavalcade in column_list, if specified, or in the table. The value listing must be enclosed in parentheses.

If the values in the Value list are not in the same club as the columns in the tabular array or do not take a value for each column in the tabular array, column_list must be used to explicitly specify the column that stores each incoming value.

Y'all can use the Transact-SQL row constructor (also chosen a table value constructor) to specify multiple rows in a single INSERT statement. The row constructor consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma. For more information, come across Tabular array Value Constructor (Transact-SQL).

Annotation

Tabular array value constructor is not supported in Azure Synapse Analytics. Instead, subsequent INSERT statements tin be executed to insert multiple rows. In Azure Synapse Analytics, insert values tin can only be constant literal values or variable references. To insert a non-literal, ready a variable to non-abiding value and insert the variable.

DEFAULT
Forces the Database Engine to load the default value divers for a column. If a default does not exist for the column and the cavalcade allows null values, NULL is inserted. For a column defined with the timestamp data type, the adjacent timestamp value is inserted. DEFAULT is not valid for an identity column.

expression
Is a constant, a variable, or an expression. The expression cannot comprise an EXECUTE statement.

When referencing the Unicode grapheme information types nchar, nvarchar, and ntext, 'expression' should exist prefixed with the uppercase letter 'N'. If 'North' is non specified, SQL Server converts the string to the code page that corresponds to the default collation of the database or column. Any characters not found in this code page are lost.

derived_table
Is any valid SELECT statement that returns rows of data to exist loaded into the table. The SELECT statement cannot comprise a mutual table expression (CTE).

execute_statement
Is any valid EXECUTE statement that returns data with SELECT or READTEXT statements. For more information, see EXECUTE (Transact-SQL).

The Outcome SETS options of the EXECUTE statement cannot exist specified in an INSERT...EXEC statement.

If execute_statement is used with INSERT, each event set must be compatible with the columns in the table or in column_list.

execute_statement can be used to execute stored procedures on the aforementioned server or a remote server. The process in the remote server is executed, and the result sets are returned to the local server and loaded into the table in the local server. In a distributed transaction, execute_statement cannot exist issued confronting a loopback linked server when the connection has multiple active result sets (MARS) enabled.

If execute_statement returns information with the READTEXT statement, each READTEXT statement tin return a maximum of 1 MB (1024 KB) of information. execute_statement can also be used with extended procedures. execute_statement inserts the data returned by the main thread of the extended procedure; notwithstanding, output from threads other than the main thread are not inserted.

You lot cannot specify a tabular array-valued parameter as the target of an INSERT EXEC argument; notwithstanding, it can be specified equally a source in the INSERT EXEC string or stored-procedure. For more data, run into Use Table-Valued Parameters (Database Engine).

<dml_table_source>
Specifies that the rows inserted into the target tabular array are those returned by the OUTPUT clause of an INSERT, UPDATE, DELETE, or MERGE statement, optionally filtered by a WHERE clause. If <dml_table_source> is specified, the target of the outer INSERT statement must meet the following restrictions:

  • Information technology must be a base table, not a view.

  • It cannot exist a remote table.

  • Information technology cannot have any triggers defined on it.

  • Information technology cannot participate in any principal key-foreign fundamental relationships.

  • Information technology cannot participate in merge replication or updatable subscriptions for transactional replication.

The compatibility level of the database must be set to 100 or college. For more information, encounter OUTPUT Clause (Transact-SQL).

<select_list>
Is a comma-separated list specifying which columns returned past the OUTPUT clause to insert. The columns in <select_list> must exist compatible with the columns into which values are being inserted. <select_list> cannot reference aggregate functions or TEXTPTR.

Note

Any variables listed in the SELECT list refer to their original values, regardless of any changes made to them in <dml_statement_with_output_clause>.

<dml_statement_with_output_clause>
Is a valid INSERT, UPDATE, DELETE, or MERGE statement that returns affected rows in an OUTPUT clause. The argument cannot contain a WITH clause, and cannot target remote tables or partitioned views. If UPDATE or DELETE is specified, it cannot be a cursor-based UPDATE or DELETE. Source rows cannot exist referenced as nested DML statements.

WHERE <search_condition>
Is whatsoever WHERE clause containing a valid <search_condition> that filters the rows returned past <dml_statement_with_output_clause>. For more information, see Search Condition (Transact-SQL). When used in this context, <search_condition> cannot incorporate subqueries, scalar user-divers functions that perform data access, aggregate functions, TEXTPTR, or total-text search predicates.

DEFAULT VALUES
Applies to: SQL Server 2008 and afterwards.

Forces the new row to comprise the default values defined for each column.

Majority
Applies to: SQL Server 2008 and later.

Used by external tools to upload a binary data stream. This option is not intended for utilize with tools such as SQL Server Direction Studio, SQLCMD, OSQL, or information access application programming interfaces such as SQL Server Native Client.

FIRE_TRIGGERS
Applies to: SQL Server 2008 and later.

Specifies that whatsoever insert triggers defined on the destination tabular array execute during the binary information stream upload operation. For more information, see Bulk INSERT (Transact-SQL).

CHECK_CONSTRAINTS
Applies to: SQL Server 2008 and later.

Specifies that all constraints on the target table or view must be checked during the binary data stream upload operation. For more information, see Bulk INSERT (Transact-SQL).

KEEPNULLS
Applies to: SQL Server 2008 and later on.

Specifies that empty columns should retain a null value during the binary information stream upload operation. For more than data, see Continue Nulls or Use Default Values During Majority Import (SQL Server).

KILOBYTES_PER_BATCH = kilobytes_per_batch
Specifies the approximate number of kilobytes (KB) of information per batch as kilobytes_per_batch. For more data, see BULK INSERT (Transact-SQL).

ROWS_PER_BATCH =rows_per_batch
Applies to: SQL Server 2008 and afterwards.

Indicates the guess number of rows of data in the binary data stream. For more information, run into Majority INSERT (Transact-SQL).

Notation

A syntax mistake is raised if a cavalcade list is non provided.

Remarks

For information specific to inserting data into SQL graph tables, come across INSERT (SQL Graph).

All-time Practices

Apply the @@ROWCOUNT office to return the number of inserted rows to the customer application. For more information, see @@ROWCOUNT (Transact-SQL).

Best Practices for Bulk Importing Data

Using INSERT INTO...SELECT to Bulk Import data with minimal logging and parallelism

Y'all can use INSERT INTO <target_table> SELECT <columns> FROM <source_table> to efficiently transfer a large number of rows from one table, such every bit a staging tabular array, to some other tabular array with minimal logging. Minimal logging tin can improve the performance of the statement and reduce the possibility of the performance filling the available transaction log space during the transaction.

Minimal logging for this statement has the following requirements:

  • The recovery model of the database is set to simple or majority-logged.
  • The target table is an empty or not-empty heap.
  • The target tabular array is not used in replication.
  • The TABLOCK hint is specified for the target tabular array.

Rows that are inserted into a heap as the result of an insert action in a MERGE statement may as well be minimally logged.

Unlike the BULK INSERT argument, which holds a less restrictive Majority Update (BU) lock, INSERT INTO … SELECT with the TABLOCK hint holds an exclusive (X) lock on the table. This means that you cannot insert rows using multiple insert operations executing simultaneously.

However, starting with SQL Server 2016 (xiii.x) and database compatibility level 130, a single INSERT INTO … SELECT statement tin exist executed in parallel when inserting into heaps or clustered columnstore indexes (CCI). Parallel inserts are possible when using the TABLOCK hint.

Parallelism for the statement in a higher place has the post-obit requirements, which are similar to the requirements for minimal logging:

  • The target table is an empty or non-empty heap.
  • The target table has a clustered columnstore alphabetize (CCI) but no not-clustered indexes.
  • The target table does not have an identity column with IDENTITY_INSERT gear up to OFF.
  • The TABLOCK hint is specified for the target table.

For scenarios where requirements for minimal logging and parallel insert are met, both improvements will work together to ensure maximum throughput of your data load operations.

Note

Inserts into local temporary tables (identified past the # prefix) and global temporary tables (identified past ## prefixes) are too enabled for parallelism using the TABLOCK hint.

Using OPENROWSET and Majority to Bulk Import information

The OPENROWSET function tin have the post-obit table hints, which provide bulk-load optimizations with the INSERT statement:

  • The TABLOCK hint can minimize the number of log records for the insert operation. The recovery model of the database must be set to simple or bulk-logged and the target table cannot exist used in replication. For more data, see Prerequisites for Minimal Logging in Bulk Import.
  • The TABLOCK hint can enable parallel insert operations. The target table is a heap or clustered columnstore index (CCI) with no non-clustered indexes, and the target table cannot take an identity column specified.
  • The IGNORE_CONSTRAINTS hint can temporarily disable Foreign KEY and Check constraint checking.
  • The IGNORE_TRIGGERS hint tin can temporarily disable trigger execution.
  • The KEEPDEFAULTS hint allows the insertion of a table cavalcade's default value, if whatever, instead of Zippo when the data tape lacks a value for the column.
  • The KEEPIDENTITY hint allows the identity values in the imported information file to be used for the identity cavalcade in the target table.

These optimizations are similar to those available with the Majority INSERT command. For more information, see Table Hints (Transact-SQL).

Data Types

When you insert rows, consider the following data type beliefs:

  • If a value is being loaded into columns with a char, varchar, or varbinary data type, the padding or truncation of trailing blanks (spaces for char and varchar, zeros for varbinary) is determined past the SET ANSI_PADDING setting defined for the column when the tabular array was created. For more data, run into Fix ANSI_PADDING (Transact-SQL).

    The post-obit table shows the default operation for Ready ANSI_PADDING OFF.

    Data blazon Default operation
    char Pad value with spaces to the defined width of column.
    varchar Remove abaft spaces to the last non-space grapheme or to a unmarried-infinite character for strings made upward of only spaces.
    varbinary Remove trailing zeros.

  • If an empty cord (' ') is loaded into a cavalcade with a varchar or text data type, the default operation is to load a zero-length cord.

  • Inserting a zero value into a text or image cavalcade does non create a valid text pointer, nor does information technology preallocate an 8-KB text page.

  • Columns created with the uniqueidentifier data blazon store specially formatted xvi-byte binary values. Unlike with identity columns, the Database Engine does not automatically generate values for columns with the uniqueidentifier information type. During an insert operation, variables with a data type of uniqueidentifier and string constants in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (36 characters including hyphens, where 10 is a hexadecimal digit in the range 0-nine or a-f) can be used for uniqueidentifier columns. For instance, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid value for a uniqueidentifier variable or cavalcade. Use the NEWID() function to obtain a globally unique ID (GUID).

Inserting Values into User-Defined Type Columns

You tin insert values in user-divers blazon columns by:

  • Supplying a value of the user-defined type.

  • Supplying a value in a SQL Server system data type, every bit long every bit the user-defined type supports implicit or explicit conversion from that type. The following example shows how to insert a value in a column of user-defined type Betoken, by explicitly converting from a string.

                      INSERT INTO Cities (Location)   VALUES ( CONVERT(Signal, '12.three:46.2') );

    A binary value tin can too exist supplied without performing explicit conversion, because all user-divers types are implicitly convertible from binary.

  • Calling a user-defined function that returns a value of the user-defined blazon. The following example uses a user-divers function CreateNewPoint() to create a new value of user-defined type Signal and insert the value into the Cities tabular array.

                      INSERT INTO Cities (Location)   VALUES ( dbo.CreateNewPoint(x, y) );

Fault Handling

Y'all can implement mistake handling for the INSERT statement by specifying the statement in a TRY...CATCH construct.

If an INSERT statement violates a constraint or rule, or if it has a value incompatible with the data blazon of the column, the statement fails and an fault message is returned.

If INSERT is loading multiple rows with SELECT or EXECUTE, any violation of a rule or constraint that occurs from the values being loaded causes the statement to be stopped, and no rows are loaded.

When an INSERT argument encounters an arithmetic error (overflow, divide past zero, or a domain fault) occurring during expression evaluation, the Database Engine handles these errors equally if Gear up ARITHABORT is set to ON. The batch is stopped, and an error message is returned. During expression evaluation when SET ARITHABORT and Fix ANSI_WARNINGS are OFF, if an INSERT, DELETE or UPDATE argument encounters an arithmetic mistake, overflow, divide-by-zero, or a domain error, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update activity fails and the user receives an fault.

Interoperability

When an INSTEAD OF trigger is defined on INSERT actions against a table or view, the trigger executes instead of the INSERT argument. For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).

Limitations and Restrictions

When you lot insert values into remote tables and non all values for all columns are specified, you must identify the columns to which the specified values are to be inserted.

When Acme is used with INSERT the referenced rows are non arranged in whatever order and the Gild By clause can not be straight specified in this statements. If yous need to utilise TOP to insert rows in a meaningful chronological order, you must utilise TOP together with an Order By clause that is specified in a subselect statement. Meet the Examples department that follows in this topic.

INSERT queries that use SELECT with Guild BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted.

In Parallel Data Warehouse, the Gild Past clause is invalid in VIEWS, CREATE TABLE AS SELECT, INSERT SELECT, inline functions, derived tables, subqueries and mutual table expressions, unless TOP is likewise specified.

Logging Behavior

The INSERT argument is e'er fully logged except when using the OPENROWSET function with the BULK keyword or when using INSERT INTO <target_table> SELECT <columns> FROM <source_table>. These operations tin exist minimally logged. For more information, see the section "Best Practices for Bulk Loading Information" earlier in this topic.

Security

During a linked server connectedness, the sending server provides a login name and password to connect to the receiving server on its behalf. For this connectedness to work, you must create a login mapping betwixt the linked servers by using sp_addlinkedsrvlogin.

When you use OPENROWSET(BULK...), it is important to understand how SQL Server handles impersonation. For more data, run into "Security Considerations" in Import Bulk Information by Using Bulk INSERT or OPENROWSET(Majority...) (SQL Server).

Permissions

INSERT permission is required on the target table.

INSERT permissions default to members of the sysadmin fixed server office, the db_owner and db_datawriter stock-still database roles, and the table owner. Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner can transfer permissions to other users.

To execute INSERT with the OPENROWSET part BULK option, yous must exist a member of the sysadmin fixed server role or of the bulkadmin fixed server role.

Examples

Category Featured syntax elements
Basic syntax INSERT • table value constructor
Treatment cavalcade values IDENTITY • NEWID • default values • user-divers types
Inserting data from other tables INSERT...SELECT • INSERT...EXECUTE • WITH common table expression • Superlative • Showtime FETCH
Specifying target objects other than standard tables Views • table variables
Inserting rows into a remote table Linked server • OPENQUERY rowset part • OPENDATASOURCE rowset function
Majority loading information from tables or data files INSERT...SELECT • OPENROWSET function
Overriding the default beliefs of the query optimizer by using hints Table hints
Capturing the results of the INSERT argument OUTPUT clause

Basic Syntax

Examples in this department demonstrate the basic functionality of the INSERT statement using the minimum required syntax.

A. Inserting a unmarried row of data

The following instance inserts 1 row into the Production.UnitMeasure table in the AdventureWorks2012 database. The columns in this table are UnitMeasureCode, Proper noun, and ModifiedDate. Considering values for all columns are supplied and are listed in the same lodge as the columns in the table, the column names do not have to exist specified in the column listing*.*

              INSERT INTO Product.UnitMeasure   VALUES (North'FT', Due north'Feet', '20080414');

B. Inserting multiple rows of information

The following case uses the table value constructor to insert 3 rows into the Production.UnitMeasure table in the AdventureWorks2012 database in a single INSERT statement. Considering values for all columns are supplied and are listed in the same lodge as the columns in the table, the cavalcade names practise not have to exist specified in the column list.

Note

The table vaule constructor is not supported in Azure Synapse Analytics.

              INSERT INTO Production.UnitMeasure   VALUES (N'FT2', N'Square Feet ', '20080923'), (North'Y', Northward'Yards', '20080923')     , (N'Y3', N'Cubic Yards', '20080923');

C. Inserting information that is not in the same lodge every bit the table columns

The post-obit example uses a column list to explicitly specify the values that are inserted into each cavalcade. The column lodge in the Production.UnitMeasure table in the AdventureWorks2012 database is UnitMeasureCode, Name, ModifiedDate; however, the columns are not listed in that order in column_list.

              INSERT INTO Production.UnitMeasure (Proper name, UnitMeasureCode,       ModifiedDate)   VALUES (N'Foursquare Yards', Due north'Y2', GETDATE());

Handling Column Values

Examples in this section demonstrate methods of inserting values into columns that are defined with an IDENTITY property, DEFAULT value, or are defined with data types such as uniqueidentifer or user-defined type columns.

D. Inserting data into a table with columns that have default values

The following example shows inserting rows into a table with columns that automatically generate a value or have a default value. Column_1 is a computed column that automatically generates a value past concatenating a cord with the value inserted into column_2. Column_2 is defined with a default constraint. If a value is non specified for this column, the default value is used. Column_3 is divers with the rowversion data blazon, which automatically generates a unique, incrementing binary number. Column_4 does non automatically generate a value. When a value for this cavalcade is non specified, NULL is inserted. The INSERT statements insert rows that comprise values for some of the columns but not all. In the last INSERT statement, no columns are specified and only the default values are inserted by using the DEFAULT VALUES clause.

              CREATE Table dbo.T1    (       column_1 Equally 'Computed column ' + column_2,        column_2 varchar(xxx)            CONSTRAINT default_name DEFAULT ('my cavalcade default'),       column_3 rowversion,       column_4 varchar(40) NULL   );   GO   INSERT INTO dbo.T1 (column_4)        VALUES ('Explicit value');   INSERT INTO dbo.T1 (column_2, column_4)        VALUES ('Explicit value', 'Explicit value');   INSERT INTO dbo.T1 (column_2)        VALUES ('Explicit value');   INSERT INTO T1 DEFAULT VALUES;    Go   SELECT column_1, column_2, column_3, column_4   FROM dbo.T1;   GO

East. Inserting data into a table with an identity cavalcade

The following case shows unlike methods of inserting data into an identity column. The beginning two INSERT statements allow identity values to be generated for the new rows. The third INSERT argument overrides the IDENTITY property for the column with the Prepare IDENTITY_INSERT statement and inserts an explicit value into the identity column.

              CREATE Tabular array dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(xxx));   GO   INSERT T1 VALUES ('Row #i');   INSERT T1 (column_2) VALUES ('Row #2');   Get   SET IDENTITY_INSERT T1 ON;   GO   INSERT INTO T1 (column_1,column_2)        VALUES (-99, 'Explicit identity value');   GO   SELECT column_1, column_2   FROM T1;   GO

F. Inserting information into a uniqueidentifier cavalcade by using NEWID()

The following case uses the NEWID() function to obtain a GUID for column_2. Dissimilar for identity columns, the Database Engine does not automatically generate values for columns with the uniqueidentifier data blazon, equally shown by the second INSERT statement.

              CREATE TABLE dbo.T1    (       column_1 int IDENTITY,        column_2 uniqueidentifier,   );   GO   INSERT INTO dbo.T1 (column_2)        VALUES (NEWID());   INSERT INTO T1 DEFAULT VALUES;    GO   SELECT column_1, column_2   FROM dbo.T1;

Thousand. Inserting data into user-divers type columns

The following Transact-SQL statements insert 3 rows into the PointValue column of the Points tabular array. This column uses a CLR user-defined type (UDT). The Point data blazon consists of X and Y integer values that are exposed every bit backdrop of the UDT. You must use either the Bandage or CONVERT part to cast the comma-delimited X and Y values to the Point type. The first two statements use the Convert function to convert a string value to the Point type, and the third statement uses the Bandage function. For more data, see Manipulating UDT Data.

              INSERT INTO dbo.Points (PointValue) VALUES (Convert(Point, 'three,4'));   INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,v'));   INSERT INTO dbo.Points (PointValue) VALUES (Cast ('1,99' AS Point));

Inserting Data from Other Tables

Examples in this section demonstrate methods of inserting rows from 1 table into another tabular array.

H. Using the SELECT and EXECUTE options to insert information from other tables

The post-obit case shows how to insert information from ane table into some other tabular array by using INSERT...SELECT or INSERT...EXECUTE. Each is based on a multi-tabular array SELECT argument that includes an expression and a literal value in the column list.

The beginning INSERT statement uses a SELECT statement to derive the information from the source tables (Employee, SalesPerson, and Person) in the AdventureWorks2012 database and store the result set up in the EmployeeSales table. The second INSERT statement uses the EXECUTE clause to call a stored process that contains the SELECT statement, and the 3rd INSERT uses the EXECUTE clause to reference the SELECT statement as a literal string.

              CREATE TABLE dbo.EmployeeSales   ( DataSource   varchar(20) NOT NULL,     BusinessEntityID   varchar(11) Not NULL,     LastName     varchar(40) NOT Zilch,     SalesDollars money NOT Nil   );   GO   CREATE Process dbo.uspGetEmployeeSales    Equally        Fix NOCOUNT ON;       SELECT 'Process', sp.BusinessEntityID, c.LastName,            sp.SalesYTD        FROM Sales.SalesPerson AS sp         INNER Join Person.Person As c           ON sp.BusinessEntityID = c.BusinessEntityID       WHERE sp.BusinessEntityID LIKE 'two%'       Guild By sp.BusinessEntityID, c.LastName;   Become   --INSERT...SELECT example   INSERT INTO dbo.EmployeeSales       SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD        FROM Sales.SalesPerson Equally sp       INNER Join Person.Person AS c           ON sp.BusinessEntityID = c.BusinessEntityID       WHERE sp.BusinessEntityID LIKE '2%'       ORDER BY sp.BusinessEntityID, c.LastName;   Become   --INSERT...EXECUTE process case   INSERT INTO dbo.EmployeeSales    EXECUTE dbo.uspGetEmployeeSales;   GO   --INSERT...EXECUTE('string') example   INSERT INTO dbo.EmployeeSales    EXECUTE    ('   SELECT ''EXEC Cord'', sp.BusinessEntityID, c.LastName,        sp.SalesYTD        FROM Sales.SalesPerson As sp        INNER JOIN Person.Person As c           ON sp.BusinessEntityID = c.BusinessEntityID       WHERE sp.BusinessEntityID Similar ''2%''       Order Past sp.BusinessEntityID, c.LastName   ');   GO   --Testify results.   SELECT DataSource,BusinessEntityID,LastName,SalesDollars   FROM dbo.EmployeeSales;

I. Using WITH mutual table expression to ascertain the data inserted

The following example creates the NewEmployee tabular array in the AdventureWorks2012 database. A mutual table expression (EmployeeTemp) defines the rows from 1 or more tables to be inserted into the NewEmployee table. The INSERT statement references the columns in the common table expression.

              CREATE TABLE HumanResources.NewEmployee   (       EmployeeID int NOT Nil,       LastName nvarchar(50) Non NULL,       FirstName nvarchar(50) Not Cypher,       PhoneNumber Phone NULL,       AddressLine1 nvarchar(lx) NOT NULL,       City nvarchar(30) Not Zero,       Country nchar(3) NOT NULL,        PostalCode nvarchar(15) Not Nil,       CurrentFlag Flag   );   Get   WITH EmployeeTemp (EmpID, LastName, FirstName, Phone,                       Accost, City, StateProvince,                       PostalCode, CurrentFlag)   Every bit (SELECT           east.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,          a.AddressLine1, a.City, sp.StateProvinceCode,           a.PostalCode, e.CurrentFlag       FROM HumanResources.Employee e           INNER JOIN Person.BusinessEntityAddress As bea           ON eastward.BusinessEntityID = bea.BusinessEntityID           INNER Bring together Person.Address Equally a           ON bea.AddressID = a.AddressID           INNER Bring together Person.PersonPhone AS pp           ON east.BusinessEntityID = pp.BusinessEntityID           INNER Bring together Person.StateProvince AS sp           ON a.StateProvinceID = sp.StateProvinceID           INNER JOIN Person.Person as c           ON due east.BusinessEntityID = c.BusinessEntityID       )   INSERT INTO HumanResources.NewEmployee        SELECT EmpID, LastName, FirstName, Phone,               Address, City, StateProvince, PostalCode, CurrentFlag       FROM EmployeeTemp;   Become

J. Using TOP to limit the data inserted from the source table

The following example creates the table EmployeeSales and inserts the proper noun and year-to-date sales information for the tiptop five random employees from the table HumanResources.Employee in the AdventureWorks2012 database. The INSERT argument chooses any 5 rows returned by the SELECT statement. The OUTPUT clause displays the rows that are inserted into the EmployeeSales tabular array. Observe that the ORDER BY clause in the SELECT statement is non used to determine the peak five employees.

              CREATE Table dbo.EmployeeSales   ( EmployeeID   nvarchar(11) Not NULL,     LastName     nvarchar(20) NOT NULL,     FirstName    nvarchar(twenty) NOT Zero,     YearlySales  money NOT NULL    );   GO   INSERT Superlative(v)INTO dbo.EmployeeSales       OUTPUT inserted.EmployeeID, inserted.FirstName,          inserted.LastName, inserted.YearlySales       SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD        FROM Sales.SalesPerson AS sp       INNER JOIN Person.Person Every bit c           ON sp.BusinessEntityID = c.BusinessEntityID       WHERE sp.SalesYTD > 250000.00       ORDER BY sp.SalesYTD DESC;

If you have to employ TOP to insert rows in a meaningful chronological order, yous must utilise TOP together with ORDER BY in a subselect statement as shown in the post-obit example. The OUTPUT clause displays the rows that are inserted into the EmployeeSales table. Notice that the peak 5 employees are now inserted based on the results of the Guild By clause instead of random rows.

              INSERT INTO dbo.EmployeeSales       OUTPUT inserted.EmployeeID, inserted.FirstName,          inserted.LastName, inserted.YearlySales       SELECT TOP (5) sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD        FROM Sales.SalesPerson AS sp       INNER Bring together Person.Person AS c           ON sp.BusinessEntityID = c.BusinessEntityID       WHERE sp.SalesYTD > 250000.00       Guild BY sp.SalesYTD DESC;

Specifying Target Objects Other Than Standard Tables

Examples in this department demonstrate how to insert rows by specifying a view or table variable.

One thousand. Inserting data by specifying a view

The following instance specifies a view name as the target object; however, the new row is inserted in the underlying base tabular array. The gild of the values in the INSERT statement must lucifer the cavalcade guild of the view. For more data, see Alter Data Through a View.

              CREATE TABLE T1 ( column_1 int, column_2 varchar(30));   GO   CREATE VIEW V1 AS    SELECT column_2, column_1    FROM T1;   GO   INSERT INTO V1        VALUES ('Row ane',1);   Get   SELECT column_1, column_2    FROM T1;   GO   SELECT column_1, column_2   FROM V1;   Go

L. Inserting information into a tabular array variable

The following case specifies a table variable as the target object in the AdventureWorks2012 database.

              -- Create the table variable.   DECLARE @MyTableVar table(       LocationID int NOT Goose egg,       CostRate smallmoney NOT Naught,       NewCostRate As CostRate * ane.v,       ModifiedDate datetime);      -- Insert values into the table variable.   INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)       SELECT LocationID, CostRate, GETDATE()      FROM Production.Location       WHERE CostRate > 0;      -- View the table variable result set.   SELECT * FROM @MyTableVar;   GO

Inserting Rows into a Remote Table

Examples in this department demonstrate how to insert rows into a remote target table by using a linked server or a rowset function to reference the remote table.

M. Inserting data into a remote tabular array by using a linked server

The following example inserts rows into a remote tabular array. The example begins by creating a link to the remote data source by using sp_addlinkedserver. The linked server proper noun, MyLinkServer, is then specified as part of the four-part object name in the class server.catalog.schema.object.

Applies to: SQL Server 2008 and subsequently.

              Use master;   GO   -- Create a link to the remote information source.    -- Specify a valid server proper noun for @datasrc as 'server_name'  -- or 'server_nameinstance_name'.      EXEC sp_addlinkedserver @server = N'MyLinkServer',       @srvproduct = N' ',       @provider = North'SQLNCLI',        @datasrc = N'server_name',       @itemize = Due north'AdventureWorks2012';   Get                          
              -- Specify the remote data source in the FROM clause using a four-part proper noun    -- in the form linked_server.catalog.schema.object.      INSERT INTO MyLinkServer.AdventureWorks2012.HumanResources.Department (Name, GroupName)   VALUES (N'Public Relations', N'Executive General and Administration');   Go

N. Inserting data into a remote tabular array by using the OPENQUERY function

The post-obit instance inserts a row into a remote table by specifying the OPENQUERY rowset function. The linked server proper name created in the previous instance is used in this case.

Applies to: SQL Server 2008 and later on.

              INSERT OPENQUERY (MyLinkServer,      'SELECT Name, GroupName       FROM AdventureWorks2012.HumanResources.Department')   VALUES ('Ecology Impact', 'Engineering');   Go

O. Inserting data into a remote tabular array by using the OPENDATASOURCE role

The following example inserts a row into a remote table by specifying the OPENDATASOURCE rowset function. Specify a valid server name for the data source by using the format server_name or server_name\instance_name.

Applies to: SQL Server 2008 and later on.

              -- Use the OPENDATASOURCE office to specify the remote data source.   -- Specify a valid server name for Data Source using the format  -- server_name or server_nameinstance_name.      INSERT INTO OPENDATASOURCE('SQLNCLI',       'Data Source= <server_name>; Integrated Security=SSPI')       .AdventureWorks2012.HumanResources.Department (Name, GroupName)       VALUES (N'Standards and Methods', 'Quality Assurance');   Get

P. Inserting into an external tabular array created using PolyBase

Export information from SQL Server to Hadoop or Azure Storage. Starting time, create an external table that points to the destination file or directory. Then, apply INSERT INTO to export data from a local SQL Server table to an external data source. The INSERT INTO statement creates the destination file or directory if it does not exist and the results of the SELECT statement are exported to the specified location in the specified file format. For more information, see Get started with PolyBase.

Applies to: SQL Server.

              -- Create an external tabular array.    CREATE EXTERNAL TABLE [dbo].[FastCustomers2009] (           [FirstName] char(25) Not Cypher,            [LastName] char(25) Non Cipher,            [YearlyIncome] bladder NULL,            [MaritalStatus] char(ane) NOT Nix   )   WITH (           LOCATION='/old_data/2009/customerdata.tbl',           DATA_SOURCE = HadoopHDP2,           FILE_FORMAT = TextFileFormat,           REJECT_TYPE = VALUE,           REJECT_VALUE = 0   );      -- Export data: Motility onetime data to Hadoop while keeping  -- it query-able via external table.    INSERT INTO dbo.FastCustomer2009   SELECT T.* FROM Insured_Customers T1 Join CarSensor_Data T2   ON (T1.CustomerKey = T2.CustomerKey)   WHERE T2.YearMeasured = 2009 and T2.Speed > forty;

Bulk Loading Data from Tables or Data Files

Examples in this section demonstrate two methods to bulk load data into a table past using the INSERT statement.

Q. Inserting data into a heap with minimal logging

The post-obit case creates a new table (a heap) and inserts data from another tabular array into it using minimal logging. The example assumes that the recovery model of the AdventureWorks2012 database is set to FULL. To ensure minimal logging is used, the recovery model of the AdventureWorks2012 database is ready to BULK_LOGGED before rows are inserted and reset to FULL later the INSERT INTO...SELECT statement. In addition, the TABLOCK hint is specified for the target table Sales.SalesHistory. This ensures that the statement uses minimal space in the transaction log and performs efficiently.

              -- Create the target heap.   CREATE Table Sales.SalesHistory(       SalesOrderID int NOT NULL,       SalesOrderDetailID int Non NULL,       CarrierTrackingNumber nvarchar(25) NULL,       OrderQty smallint Not NULL,       ProductID int Not Nada,       SpecialOfferID int Non Naught,       UnitPrice money NOT NULL,       UnitPriceDiscount coin NOT NULL,       LineTotal coin Not NULL,       rowguid uniqueidentifier ROWGUIDCOL  NOT Goose egg,       ModifiedDate datetime NOT Nix );   GO   -- Temporarily set the recovery model to BULK_LOGGED.   ALTER DATABASE AdventureWorks2012   SET RECOVERY BULK_LOGGED;   GO   -- Transfer data from Sales.SalesOrderDetail to Sales.SalesHistory   INSERT INTO Sales.SalesHistory WITH (TABLOCK)       (SalesOrderID,         SalesOrderDetailID,        CarrierTrackingNumber,         OrderQty,         ProductID,         SpecialOfferID,         UnitPrice,         UnitPriceDiscount,        LineTotal,         rowguid,         ModifiedDate)   SELECT * FROM Sales.SalesOrderDetail;   GO   -- Reset the recovery model.   Alter DATABASE AdventureWorks2012   SET RECOVERY Total;   GO

R. Using the OPENROWSET function with BULK to bulk load data into a table

The following example inserts rows from a data file into a table by specifying the OPENROWSET function. The IGNORE_TRIGGERS table hint is specified for performance optimization. For more examples, see Import Majority Data by Using Majority INSERT or OPENROWSET(Majority...) (SQL Server).

Applies to: SQL Server 2008 and later.

              INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Proper noun, GroupName)   SELECT b.Name, b.GroupName    FROM OPENROWSET (       BULK 'C:SQLFilesDepartmentData.txt',       FORMATFILE = 'C:SQLFilesBulkloadFormatFile.xml',       ROWS_PER_BATCH = 15000)AS b ;

Overriding the Default Behavior of the Query Optimizer by Using Hints

Examples in this section demonstrate how to utilize table hints to temporarily override the default behavior of the query optimizer when processing the INSERT statement.

Caution

Because the SQL Server query optimizer typically selects the best execution program for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.

Due south. Using the TABLOCK hint to specify a locking method

The following example specifies that an exclusive (10) lock is taken on the Production.Location table and is held until the end of the INSERT statement.

Applies to: SQL Server, SQL Database.

              INSERT INTO Production.Location WITH (XLOCK)   (Name, CostRate, Availability)   VALUES ( Northward'Last Inventory', xv.00, 80.00);

Capturing the Results of the INSERT Statement

Examples in this section demonstrate how to use the OUTPUT Clause to return information from, or expressions based on, each row affected past an INSERT statement. These results can be returned to the processing application for apply in such things as confirmation messages, archiving, and other such application requirements.

T. Using OUTPUT with an INSERT argument

The following instance inserts a row into the ScrapReason table and uses the OUTPUT clause to return the results of the statement to the @MyTableVar tabular array variable. Considering the ScrapReasonID column is defined with an IDENTITY property, a value is not specified in the INSERT statement for that column. Even so, notation that the value generated by the Database Engine for that column is returned in the OUTPUT clause in the INSERTED.ScrapReasonID column.

              DECLARE @MyTableVar tabular array( NewScrapReasonID smallint,                              Name varchar(l),                              ModifiedDate datetime);   INSERT Production.ScrapReason       OUTPUT INSERTED.ScrapReasonID, INSERTED.Proper name, INSERTED.ModifiedDate           INTO @MyTableVar   VALUES (N'Operator fault', GETDATE());      --Display the result set of the table variable.   SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;   --Display the outcome ready of the tabular array.   SELECT ScrapReasonID, Proper noun, ModifiedDate    FROM Production.ScrapReason;

U. Using OUTPUT with identity and computed columns

The following example creates the EmployeeSales table and and so inserts several rows into it using an INSERT statement with a SELECT argument to call back data from source tables. The EmployeeSales table contains an identity column (EmployeeID) and a computed cavalcade (ProjectedSales). Because these values are generated by the Database Engine during the insert operation, neither of these columns can be divers in @MyTableVar.

              CREATE TABLE dbo.EmployeeSales   ( EmployeeID   int IDENTITY (1,5)Not Nothing,     LastName     nvarchar(20) Not Nada,     FirstName    nvarchar(20) Non Zip,     CurrentSales money NOT Null,     ProjectedSales Equally CurrentSales * 1.ten    );   GO   DECLARE @MyTableVar tabular array(     LastName     nvarchar(xx) NOT NULL,     FirstName    nvarchar(20) Non Nil,     CurrentSales coin NOT Goose egg     );      INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)     OUTPUT INSERTED.LastName,             INSERTED.FirstName,             INSERTED.CurrentSales     INTO @MyTableVar       SELECT c.LastName, c.FirstName, sp.SalesYTD       FROM Sales.SalesPerson AS sp       INNER JOIN Person.Person Equally c           ON sp.BusinessEntityID = c.BusinessEntityID       WHERE sp.BusinessEntityID Like '2%'       Society Past c.LastName, c.FirstName;      SELECT LastName, FirstName, CurrentSales   FROM @MyTableVar;   GO   SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales   FROM dbo.EmployeeSales;

Five. Inserting data returned from an OUTPUT clause

The following instance captures data returned from the OUTPUT clause of a MERGE statement, and inserts that data into another table. The MERGE statement updates the Quantity column of the ProductInventory tabular array daily, based on orders that are processed in the SalesOrderDetail table in the AdventureWorks2012 database. It besides deletes rows for products whose inventories drib to 0. The case captures the rows that are deleted and inserts them into some other table, ZeroInventory, which tracks products with no inventory.

              --Create ZeroInventory tabular array.   CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);   Get      INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)   SELECT ProductID, GETDATE()   FROM   (   MERGE Production.ProductInventory AS pi       USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod              Bring together Sales.SalesOrderHeader As soh              ON sod.SalesOrderID = soh.SalesOrderID              AND soh.OrderDate = '20070401'              Grouping BY ProductID) AS src (ProductID, OrderQty)       ON (pi.ProductID = src.ProductID)       WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0           THEN DELETE       WHEN MATCHED           THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty       OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)   WHERE Action = 'DELETE';   IF @@ROWCOUNT = 0   PRINT 'Warning: No rows were inserted';   Go   SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;

Westward. Inserting information using the SELECT option

The following instance shows how to insert multiple rows of data using an INSERT statement with a SELECT selection. The first INSERT statement uses a SELECT argument direct to retrieve data from the source tabular array, and then to store the result set in the EmployeeTitles table.

              CREATE TABLE EmployeeTitles   ( EmployeeKey   INT Non NULL,     LastName     varchar(40) NOT NULL,     Title      varchar(l) NOT NULL   );   INSERT INTO EmployeeTitles       SELECT EmployeeKey, LastName, Title        FROM ssawPDW.dbo.DimEmployee       WHERE EndDate IS Zero;

X. Specifying a characterization with the INSERT statement

The post-obit example shows the use of a label with an INSERT statement.

              -- Uses AdventureWorks      INSERT INTO DimCurrency    VALUES (500, Northward'C1', North'Currency1')   Option ( LABEL = North'label1' );

Y. Using a label and a query hint with the INSERT statement

This query shows the bones syntax for using a label and a query bring together hint with the INSERT statement. Subsequently the query is submitted to the Command node, SQL Server, running on the Compute nodes, will apply the hash bring together strategy when it generates the SQL Server query program. For more information on join hints and how to use the OPTION clause, run into Option (SQL Server PDW).

              -- Uses AdventureWorks      INSERT INTO DimCustomer (CustomerKey, CustomerAlternateKey,      FirstName, MiddleName, LastName )    SELECT ProspectiveBuyerKey, ProspectAlternateKey,      FirstName, MiddleName, LastName   FROM ProspectiveBuyer p JOIN DimGeography g ON p.PostalCode = grand.PostalCode   WHERE yard.CountryRegionCode = 'FR'   OPTION ( LABEL = 'Add French Prospects', HASH JOIN);

Meet Also

Bulk INSERT (Transact-SQL)
DELETE (Transact-SQL)
EXECUTE (Transact-SQL)
FROM (Transact-SQL)
IDENTITY (Property) (Transact-SQL)
NEWID (Transact-SQL)
SELECT (Transact-SQL)
UPDATE (Transact-SQL)
MERGE (Transact-SQL)
OUTPUT Clause (Transact-SQL)
Use the inserted and deleted Tables