Skip to main content This browser is no longer supported. Show
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. INSERT (Transact-SQL)
In this articleApplies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Adds one or more rows to a table or a view in SQL Server. For examples, see Examples. Transact-SQL Syntax ConventionsSyntax
ArgumentsWITH <common_table_expression> TOP (expression) [ PERCENT ] INTO server_name 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 function. 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 apply to all data sources and is subject to the capabilities of the OLE DB provider that accesses the remote object. database_name Is the name of the database. schema_name table_or
view_name A table variable, within its scope, can be used as a table source in an INSERT statement. The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view. For example, an INSERT into a multi-table view must use a column_list that references only columns from one base table. For more information about updatable views, see CREATE VIEW (Transact-SQL). rowset_function_limited Is either the OPENQUERY or OPENROWSET function. Use of these functions is subject to the capabilities of the OLE DB provider that accesses the remote object. WITH ( <table_hint_limited>
[... n ] ) READPAST, NOLOCK, and READUNCOMMITTED are not allowed. For more information about table hints, see Table Hints (Transact-SQL). Important The ability 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 affect the performance of INSERT statements. Avoid using them in new development work, and plan to modify applications that currently use them. Specifying the TABLOCK hint on a table that is the target of an INSERT statement has the same effect as specifying the TABLOCKX hint. An exclusive lock is taken on the table. (column_list) If a column is not in column_list, the Database Engine must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. The Database Engine automatically provides a value for the column if the column:
column_list must be used when explicit values are inserted into an identity column, and the SET IDENTITY_INSERT option must be ON for the table. OUTPUT
Clause The OUTPUT clause is not supported in DML statements that reference local partitioned views, distributed partitioned views, or remote tables, or INSERT statements that contain an execute_statement. The OUTPUT INTO clause is not supported in INSERT statements that contain a <dml_table_source> clause. For more information about the arguments and behavior of this clause, see OUTPUT Clause (Transact-SQL). VALUES If the values in the Value list are not in the same order as the columns in the table or do not have a value for each column in the table, column_list must be used to explicitly specify the column that stores each incoming value. You can use the Transact-SQL row constructor (also called 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, see Table Value Constructor (Transact-SQL). Note Table value constructor is not supported in Azure Synapse Analytics. Instead, subsequent DEFAULT expression When referencing the Unicode character data types nchar, nvarchar, and ntext, 'expression' should be prefixed with the capital letter 'N'. If 'N' is not 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 execute_statement The RESULT SETS options of the EXECUTE statement cannot be specified in an INSERT...EXEC statement. If execute_statement is used with INSERT, each result 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 same server or a remote server. The procedure 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 be issued against a loopback linked server when the connection has multiple active result sets (MARS) enabled. If execute_statement returns data with the READTEXT statement, each READTEXT statement can return a maximum of 1 MB (1024 KB) of data. execute_statement can also be used with extended procedures. execute_statement inserts the data returned by the main thread of the extended procedure; however, output from threads other than the main thread are not inserted. You cannot specify a table-valued parameter as the target of an INSERT EXEC statement; however, it can be specified as a source in the INSERT EXEC string or stored-procedure. For more information, see Use Table-Valued Parameters (Database Engine). <dml_table_source>
The compatibility level of the database must be set to 100 or higher. For more information, see OUTPUT Clause (Transact-SQL). <select_list> 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> WHERE <search_condition> DEFAULT VALUES Forces the new row to contain the default values defined for each column. BULK Used by external tools to upload a binary data stream. This option is not intended for use with tools such as SQL Server Management Studio, SQLCMD, OSQL, or data access application programming interfaces such as SQL Server Native Client. FIRE_TRIGGERS Specifies that any insert triggers defined on the destination table execute during the binary data stream upload operation. For more information, see BULK INSERT (Transact-SQL). CHECK_CONSTRAINTS 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 Specifies that empty columns should retain a null value during the binary data stream upload operation. For more information, see Keep Nulls or Use Default Values During Bulk Import (SQL Server). KILOBYTES_PER_BATCH = kilobytes_per_batch ROWS_PER_BATCH =rows_per_batch Indicates the approximate number of rows of data in the binary data stream. For more information, see BULK INSERT (Transact-SQL). Note A syntax error is raised if a column list is not provided. RemarksFor information specific to inserting data into SQL graph tables, see INSERT (SQL Graph). Best PracticesUse the @@ROWCOUNT function to return the number of inserted rows to the client application. For more information, see @@ROWCOUNT (Transact-SQL). Best Practices for Bulk Importing DataUsing INSERT INTO...SELECT to Bulk Import data with minimal logging and parallelismYou can use Minimal logging for this statement has the following requirements:
Rows that are inserted into a heap as the result of an insert action in a MERGE statement may also be minimally logged. Unlike the However, starting with SQL Server 2016 (13.x) and database compatibility level 130, a single Parallelism for the statement above has the following requirements, which are similar to the requirements for minimal logging:
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 by the # prefix) and global temporary tables (identified by ## prefixes) are also enabled for parallelism using the TABLOCK hint. Using OPENROWSET and BULK to Bulk Import dataThe OPENROWSET function can accept the following table hints, which provide bulk-load optimizations with the INSERT statement:
These optimizations are similar to those available with the Data TypesWhen you insert rows, consider the following data type behavior:
Inserting Values into User-Defined Type ColumnsYou can insert values in user-defined type columns by:
Error HandlingYou can implement error 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 type of the column, the statement fails and an error 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 statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, the Database Engine handles these errors as if SET ARITHABORT is set to ON. The batch is stopped, and an error message is returned. During expression evaluation when SET ARITHABORT and SET ANSI_WARNINGS are OFF, if an INSERT, DELETE or UPDATE statement encounters an arithmetic error, 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 action fails and the user receives an error. InteroperabilityWhen an Limitations and RestrictionsWhen you insert values into remote tables and not all values for all columns are specified, you must identify the columns to which the specified values are to be inserted. When TOP is used with INSERT the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in this statements. If you need to use TOP to insert rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause that is specified in a subselect statement. See the Examples section that follows in this topic. INSERT queries that use SELECT with ORDER 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 ORDER BY clause is invalid in VIEWS, CREATE TABLE AS SELECT, INSERT SELECT, inline functions, derived tables, subqueries and common table expressions, unless TOP is also specified. Logging BehaviorThe INSERT statement is always fully logged except when using the OPENROWSET function with the BULK keyword or when using SecurityDuring a linked server connection, the sending server provides a login name and password to connect to the receiving server on its behalf. For this connection to work, you must create a login mapping between the linked servers by using sp_addlinkedsrvlogin. When you use OPENROWSET(BULK...), it is important to understand how SQL Server handles impersonation. For more information, see "Security Considerations" in Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server). PermissionsINSERT permission is required on the target table. INSERT permissions default to members of the To execute INSERT with the OPENROWSET function BULK option, you must be a member of the Examples
Basic SyntaxExamples in this section demonstrate the basic functionality of the INSERT statement using the minimum required syntax. A. Inserting a single row of dataThe following example inserts one row into the
B. Inserting multiple rows of dataThe following example uses the table value constructor to insert three rows into the Note The table vaule constructor is not supported in Azure Synapse Analytics.
C. Inserting data that is not in the same order as the table columnsThe
following example uses a column list to explicitly specify the values that are inserted into each column. The column order in the
Handling Column ValuesExamples 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 valuesThe following example shows inserting rows into a table with columns that automatically generate a value or have a default value.
E. Inserting data into a table with an identity columnThe following example shows different methods of inserting data into an identity column. The first two INSERT statements allow identity values to be generated for the new rows. The third INSERT statement overrides the IDENTITY property for the column with the SET IDENTITY_INSERT statement and inserts an explicit value into the identity column.
F. Inserting data into a uniqueidentifier column by using NEWID()The following example uses the NEWID() function to obtain a GUID for
G. Inserting data into user-defined type columnsThe following Transact-SQL statements
insert three rows into the
Inserting Data from Other TablesExamples in this section demonstrate methods of inserting rows from one table into another table. H. Using the SELECT and EXECUTE options to insert data from other tablesThe following example shows how to insert data from one table into another table by using INSERT...SELECT or INSERT...EXECUTE. Each is based on a multi-table SELECT statement that includes an expression and a literal value in the column list. The first INSERT statement uses a SELECT statement to derive the data from the source tables (
I. Using WITH common table expression to define the data insertedThe following example creates the
J. Using TOP to limit the data inserted from the source tableThe following example creates the table
If you have to use TOP to insert rows in a meaningful chronological order, you must use TOP together with ORDER BY in a subselect statement as shown in the following example. The OUTPUT clause displays the rows that are inserted into the
Specifying Target Objects Other Than Standard TablesExamples in this section demonstrate how to insert rows by specifying a view or table variable. K. Inserting data by specifying a viewThe following example specifies a view name as the target object; however, the new row is inserted in the underlying base table. The order of the values in the
L. Inserting data into a table variableThe following example specifies a table variable as the target object in the AdventureWorks2019 database.
Inserting Rows into a Remote TableExamples in this section 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 table by using a linked serverThe following example inserts rows into a remote table. The example begins by creating a link to the remote data source by using
sp_addlinkedserver. The linked server name, Applies to: SQL Server 2008 (10.0.x) and later.
N. Inserting data into a remote table by using the OPENQUERY functionThe following example inserts a row into a remote table by specifying the OPENQUERY rowset function. The linked server name created in the previous example is used in this example. Applies to: SQL Server 2008 (10.0.x) and later.
O. Inserting data into a remote table by using the OPENDATASOURCE functionThe 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 (10.0.x) and later.
P. Inserting into an external table created using PolyBaseExport data from SQL Server to Hadoop or Azure Storage. First, create an external table that points to the destination file or directory. Then, use 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.
Bulk Loading Data from Tables or Data FilesExamples in this section demonstrate two methods to bulk load data into a table by using the INSERT statement. Q. Inserting data into a heap with minimal loggingThe following example creates a new table (a heap) and inserts data from another table into it using minimal logging. The example assumes that the recovery model of
the
R. Using the OPENROWSET function with BULK to bulk load data into a tableThe 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 Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server). Applies to: SQL Server 2008 (10.0.x) and later.
Overriding the Default Behavior of the Query Optimizer by Using HintsExamples in this section demonstrate how to use 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 plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators. S. Using the TABLOCK hint to specify a locking methodThe following example specifies that an exclusive (X) lock is taken on the Production.Location table and is held until the end of the INSERT statement. Applies to: SQL Server, SQL Database.
Capturing the Results of the INSERT StatementExamples in this section demonstrate how to use the OUTPUT Clause to return information from, or expressions based on, each row affected by an INSERT statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. T. Using OUTPUT with an INSERT statementThe following example inserts a row into the
U. Using OUTPUT with identity and computed columnsThe following example creates the
V. Inserting data returned from an OUTPUT clauseThe following example captures data returned from the OUTPUT clause
of a MERGE statement, and inserts that data into another table. The MERGE statement updates the
W. Inserting data using the SELECT optionThe following example shows how to insert multiple rows of data using an INSERT statement with a SELECT option. The first
X. Specifying a label with the INSERT statementThe following example shows the use of a label with an INSERT statement.
Y. Using a label and a query hint with the INSERT statementThis query shows the basic syntax for using a label and a query join hint with the INSERT statement. After the query is submitted to the Control node, SQL Server, running on the Compute nodes, will apply the hash join strategy when it generates the SQL Server query plan. For more information on join hints and how to use the OPTION clause, see OPTION (SQL Server PDW).
See AlsoBULK INSERT (Transact-SQL) Additional resourcesAdditional resourcesIn this articleWhen you write a SELECT statement you must code the four main clauses in the following order?C. The four main clauses used with a SELECT statement are: SELECT, FROM, WHERE, ORDER BY. The correct order of each clause is also important to note, as shown to give proper results when executed.
Which clause of the SELECT statement specifies the table that contains the data to be retrieved?The SELECT clause specifies one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names. To retrieve all columns, use the wild card * (an asterisk). The FROM clause specifies one or more tables to be queried.
What is the order of precedence for the logical operators in a WHERE clause?The order of precedence is: logical complements ( Not ) are performed first, logical conjunctions ( And ) are performed next, and logical disjunctions ( Or ) are performed at the end.
What keyword can you code in the SELECT clause to prevent duplicate rows from being returned?The DISTINCT keyword in the SELECT clause is used to eliminate duplicate rows and display a unique list of values. In other words, the DISTINCT keyword retrieves unique values from a table.
|