INSERT (Transact-SQL)

Article
12/03/2008

Adds a new row to a table or a view.

Topic link icon Transact-SQL Syntax Conventions

Syntax

[ WITH <common_table_expression> [ ,...n ] ]
INSERT 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO] 
    { <object> | rowset_function_limited 
      [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
    }
{
    [ ( column_list ) ] 
    [ <OUTPUT Clause> ]
    { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) 
    | derived_table 
    | execute_statement 
    } 
} 
    | DEFAULT VALUES 
[; ]

<object> ::=
{ 
    [ server_name . database_name . schema_name . 
      | database_name .[ schema_name ] . 
      | schema_name . 
    ]
        table_or_view_name
}

Arguments

WITH <common_table_expression>
Specifies the temporary named result set, also known as common table expression, defined within the scope of the INSERT statement. The result set is derived from a SELECT statement.

Common table expressions can also be used with the SELECT, DELETE, UPDATE, and CREATE VIEW statements. For more information, see WITH common_table_expression (Transact-SQL).

TOP (expression) [ PERCENT ]
Specifies the number or percent of random rows that will be inserted. expression can be either a number or a percent of the rows. The rows referenced in the TOP expression that are used with INSERT, UPDATE, or DELETE are not arranged in any order.

Parentheses delimiting expression in TOP are required in INSERT, UPDATE, and DELETE statements. For more information, see TOP (Transact-SQL).

INTO
Is an optional keyword that can be used between INSERT and the target table.

server_name
Is the name of the server (using the OPENDATASOURCE function as the server name) on which the table or view is located. If server_name is specified, database_name and schema_name are required.

database_name
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 name of the table or view that is to receive the data.

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 multitable 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.

WITH ( <table_hint_limited> [... n ] )
Specifies one or more table 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 Hint (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)
Is a list of one or more columns in which to insert data. column_list must be enclosed in parentheses and delimited by commas.

If a column is not in column_list, the SQL Server 2005 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:
- Has an IDENTITY property. The next incremental identity value is used.
- Has a default. The default value for the column is used.
- Has a timestamp data type. The current timestamp value is used.
- Is nullable. A null value is used.
- Is a computed column. The calculated value is used.
column_list and VALUES 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
Returns inserted rows as part of the insert operation. 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.

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

If the values in the VALUES 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.

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

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

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

execute_statement
Is any valid EXECUTE statement that returns data with SELECT or READTEXT statements. The SELECT statement cannot contain a CTE.

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.

SQL Server 2008 changes the transaction semantics of INSERT...EXECUTE statements that execute against a loopback linked server. In SQL Server 2005, this scenario is not supported and causes an error. In SQL Server 2008, an INSERT...EXECUTE statement can execute against a loopback linked server when the connection does not have multiple active result sets (MARS) enabled. When MARS is enabled on the connection, the behavior is the same as in SQL Server 2005.

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.

DEFAULT VALUES
Forces the new row to contain the default values defined for each column.

Remarks

INSERT appends new rows to a table. To replace data in a table, the DELETE or TRUNCATE TABLE statements must be used to clear existing data before loading new data by using INSERT. To modify column values in existing rows, use UPDATE. You can create a new table and load it with data in one step by using the INTO option of the SELECT statement.

Columns created with the uniqueidentifier data type store specially formatted 16-byte binary values. Unlike with identity columns, the Database Engine does not automatically generate values for columns with the uniqueidentifier data 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 x is a hexadecimal digit in the range 0-9 or a-f) can be used for uniqueidentifier columns. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid value for a uniqueidentifier variable or column. Use the NEWID() function to obtain a globally unique ID (GUID).

The setting of the SET ROWCOUNT option is ignored for INSERT statements against local and remote partitioned views. Also, this option is not supported for INSERT statements issued against remote tables in the Database Engine when the compatibility level is set to 80 or higher.

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 rest of the batch is stopped, and an error message is returned.

Rules for Inserting Rows

When you insert rows, the following rules apply:

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 by the SET ANSI_PADDING setting defined for the column when the table was created. For more information, see SET ANSI_PADDING (Transact-SQL).
The following table shows the default operation for SET ANSI_PADDING OFF.

Data type	Default operation
char	Pad value with spaces to the defined width of column.
varchar	Remove trailing spaces to the last nonspace character or to a single-space character for strings made up of only spaces.
varbinary	Remove trailing zeros.

If an empty string (' ') is loaded into a column with a varchar or text data type, the default operation is to load a zero-length string.
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 the Database Engine displays an error message.
Inserting a null value into a text or image column does not create a valid text pointer, nor does it preallocate an 8-KB text page. For more information about inserting text and image data, see Using text, ntext, and image Functions.
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 complete statement to be stopped, and no rows are loaded.
When you insert values into tables in a remote instance of the Database Engine, and not all values for all columns are specified, you must identify the columns to which the specified values are to be inserted.

Using INSTEAD OF Triggers on INSERT Actions

When an INSTEAD OF trigger is defined on INSERT actions against a table or view, the trigger executes instead of the INSERT statement. Earlier versions of SQL Server only support AFTER triggers defined on the INSERT and other data modification statements. For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).

Inserting Values into User-defined Type Columns

You can insert values in user-defined type columns by:

Supplying a value of the user-defined type.
Supplying a value in a SQL Server 2005 system data type, as long as 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 Point, by explicitly converting from a string.
```
INSERT INTO Cities (Location)
VALUES ( CONVERT(Point, '12.3:46.2') );
```
A binary value can also be supplied without performing explicit conversion, because all user-defined types are implicitly convertible from binary. For more information about conversion and user-defined types, see Performing Operations on User-defined Types.
Calling a user-defined function that returns a value of the user-defined type. The following example uses a user-defined function CreateNewPoint() to create a new value of user-defined type Point and insert the value into the Cities table.
```
INSERT INTO Cities (Location)
VALUES ( dbo.CreateNewPoint(x, y) );
```

Using OPENROWSET and BULK to Bulk Load Data

In the SQL Server 2005 Database Engine, new table hints that are available with the OPENROWSET bulk rowset provider offer the following bulk-load optimizations with the INSERT statement:

Bulk-load logging (minimizing the number of log records for the insert operation)
Constraint checking can be set ON or OFF
Trigger execution can be set ON or OFF

These optimizations are similar to those available with the BULK INSERT command.

When an INSERT statement performs a bulk load into a nonempty table, the following additional performance enhancements exist:

When a page is split during bulk load, the new rows added to the page do not have to be fully logged.
If there are nonclustered indexes, but no clustered index on the table, the individual index rows may have to be fully logged, but the data rows do not have to be fully logged.

For more information, see OPENROWSET (Transact-SQL) and Table Hint (Transact-SQL).

Permissions

INSERT permission is required on the target table.

INSERT permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed 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 function BULK option, you must be a member of the sysadmin fixed server role or of the bulkadmin fixed server role.

Examples

A. Using a simple INSERT statement

The following example inserts one row in the Production.UnitMeasure table. Because values for all columns are supplied and are listed in the same order as the columns in the table, the column names do not have to be specified in column_list.

USE AdventureWorks;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'F2', N'Square Feet', GETDATE());
GO

B. Inserting data that is not in the same order as the table columns

The following example uses column_list to explicitly specify the values that are inserted into each column. The column order in the UnitMeasure table is UnitMeasureCode, Name, ModifiedDate; however, the columns are not listed in that order in column_list.

USE AdventureWorks;
GO
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,
    ModifiedDate)
VALUES (N'Square Yards', N'Y2', GETDATE());
GO

C. Inserting data with fewer values than columns

The following example shows inserting rows into a table with columns that automatically generate a value or have a default value. The INSERT statements insert rows that contain 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.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 int IDENTITY, 
    column_2 varchar(30) 
        CONSTRAINT default_name DEFAULT ('my column default'),
    column_3 timestamp,
    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

D. Inserting data into a table with an identity column

The 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.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30));
GO
INSERT T1 VALUES ('Row #1');
INSERT T1 (column_2) VALUES ('Row #2');
GO
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

E. Inserting data into a uniqueidentifier column by using NEWID()

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

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
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;
GO

F. Inserting data into a table through a view

The following example specifies a view name in the INSERT statement; however, the new row is inserted in the underlying table of the view. The order of VALUES list in the INSERT statement must match the column order of the view.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
IF OBJECT_ID ('dbo.V1', 'V') IS NOT NULL
    DROP VIEW dbo.V1;
GO
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 1',1);
GO
SELECT column_1, column_2 
FROM T1;
GO
SELECT column_1, column_2
FROM V1;
GO

G. Inserting data using the SELECT and EXECUTE options

The following example shows three different methods for getting data from one table and inserting it into another. Each is based on a multitable SELECT statement that includes an expression and a literal value in the column list.

The first INSERT statement uses a SELECT statement directly to retrieve data from the source tables (Employee, SalesPerson, and Contact) and store the result set in the EmployeeSales table. The second INSERT executes a stored procedure that contains the SELECT statement, and the third INSERT executes the SELECT statement as a literal string.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
IF OBJECT_ID ('dbo.uspGetEmployeeSales', 'P') IS NOT NULL
    DROP PROCEDURE uspGetEmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( DataSource   varchar(20) NOT NULL,
  EmployeeID   varchar(11) NOT NULL,
  LastName     varchar(40) NOT NULL,
  SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales 
AS 
    SELECT 'PROCEDURE', e.EmployeeID, c.LastName, 
        sp.SalesYTD 
    FROM HumanResources.Employee AS e 
        INNER JOIN Sales.SalesPerson AS sp  
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
GO
--INSERT...SELECT example
INSERT dbo.EmployeeSales
    SELECT 'SELECT', e.EmployeeID, c.LastName, sp.SalesYTD 
    FROM HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT EmployeeSales 
EXECUTE uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT EmployeeSales 
EXECUTE 
('
SELECT ''EXEC STRING'', e.EmployeeID, c.LastName, 
    sp.SalesYTD 
    FROM HumanResources.Employee AS e 
        INNER JOIN Sales.SalesPerson AS sp 
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE ''2%''
    ORDER BY e.EmployeeID, c.LastName
');
GO
--Show results.
SELECT DataSource,EmployeeID,LastName,SalesDollars
FROM dbo.EmployeeSales;
GO

H. Inserting data by using the TOP clause

The following example creates the NewEmployee table and inserts address data for the top 10 employees from the Employee table into it. The SELECT statement is then executed to verify the contents of the NewEmployee table.

USE AdventureWorks;
GO
IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
    DROP TABLE HumanResources.NewEmployee;
GO
CREATE TABLE HumanResources.NewEmployee
(
    EmployeeID int NOT NULL,
    LastName nvarchar(50) NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    Phone Phone NULL,
    AddressLine1 nvarchar(60) NOT NULL,
    City nvarchar(30) NOT NULL,
    State nchar(3) NOT NULL, 
    PostalCode nvarchar(15) NOT NULL,
    CurrentFlag Flag
);
GO
INSERT TOP (10) INTO HumanResources.NewEmployee 
    SELECT
       e.EmployeeID, c.LastName, c.FirstName, c.Phone,
       a.AddressLine1, a.City, sp.StateProvinceCode, 
       a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN HumanResources.EmployeeAddress AS ea
        ON e.EmployeeID = ea.EmployeeID
        INNER JOIN Person.Address AS a
        ON ea.AddressID = a.AddressID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Contact as c
        ON e.ContactID = c.ContactID;
GO
SELECT  EmployeeID, LastName, FirstName, Phone,
        AddressLine1, City, State, PostalCode, CurrentFlag
FROM HumanResources.NewEmployee;
GO

I. Using OUTPUT with an INSERT statement

The following example inserts a row into the ScrapReason table and uses the OUTPUT clause to return the results of the statement to the @MyTableVartable variable. Because the ScrapReasonID column is defined with an IDENTITY property, a value is not specified in the INSERT statement for that column. However, note that the value generated by the Database Engine for that column is returned in the OUTPUT clause in the INSERTED.ScrapReasonID column.

USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

J. Using WITH Common Table Expression with an INSERT statement

The following example creates the NewEmployee table. A common table expression (EmployeeTemp) defines the rows to be inserted into the NewEmployee table. The INSERT statement references the columns in the common table expression.

USE AdventureWorks;
GO
IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
    DROP TABLE HumanResources.NewEmployee;
GO
CREATE TABLE HumanResources.NewEmployee
(
    EmployeeID int NOT NULL,
    LastName nvarchar(50) NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    Phone Phone NULL,
    AddressLine1 nvarchar(60) NOT NULL,
    City nvarchar(30) NOT NULL,
    State nchar(3) NOT NULL, 
    PostalCode nvarchar(15) NOT NULL,
    CurrentFlag Flag
);
GO
WITH EmployeeTemp (EmpID, LastName, FirstName, Phone, 
                   Address, City, StateProvince, 
                   PostalCode, CurrentFlag)
AS (SELECT 
        e.EmployeeID, c.LastName, c.FirstName, c.Phone,
        a.AddressLine1, a.City, sp.StateProvinceCode, 
        a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN HumanResources.EmployeeAddress AS ea
        ON e.EmployeeID = ea.EmployeeID
        INNER JOIN Person.Address AS a
        ON ea.AddressID = a.AddressID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Contact as c
        ON e.ContactID = c.ContactID
    )
INSERT INTO HumanResources.NewEmployee 
    SELECT EmpID, LastName, FirstName, Phone, 
           Address, City, StateProvince, PostalCode, CurrentFlag
    FROM EmployeeTemp;
GO

K. Using OUTPUT With Identity and Computed Columns

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

USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID   int IDENTITY (1,5)NOT NULL,
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL,
  ProjectedSales AS CurrentSales * 1.10 
);
GO
DECLARE @MyTableVar table(
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL
  );

INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
  OUTPUT INSERTED.LastName, 
         INSERTED.FirstName, 
         INSERTED.CurrentSales
  INTO @MyTableVar
    SELECT c.LastName, c.FirstName, sp.SalesYTD
    FROM HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY c.LastName, c.FirstName;

SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO

Share via