INSERT
Adds a new row to a table or a view.
Syntax
INSERT [ INTO]
{ table_name WITH ( < table_hint_limited > [ ...n ] )
| view_name
| rowset_function_limited
}
{ [ ( column_list ) ]
{ VALUES
( { DEFAULT | NULL | expression } [ ,...n] )
| derived_table
| execute_statement
}
}
| DEFAULT VALUES
< table_hint_limited > ::=
{ FASTFIRSTROW
| HOLDLOCK
| PAGLOCK
| READCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
}
Arguments
[INTO]
Is an optional keyword that can be used between INSERT and the target table.
table_name
Is the name of a table or table variable that is to receive the data.
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 FROM.
view_name
Is the name and optional alias of a view. The view referenced by view_name must be updatable. The modifications made by the INSERT statement cannot affect more than one of the base tables referenced 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.
rowset_function_limited
Is either the OPENQUERY or OPENROWSET function. For more information, see OPENQUERY and OPENROWSET.
(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, Microsoft® SQL Server™ must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. SQL Server 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.
column_list and VALUES list must be used when inserting explicit values into an identity column, and the SET IDENTITY_INSERT option must be ON for the table.
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 SQL Server to load the default value defined for a column. If a default does not exist for the column and the column allows NULLs, 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.
execute_statement
Is any valid EXECUTE statement that returns data with SELECT or READTEXT statements.
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. If execute_statement returns data with the READTEXT statement, each individual READTEXT statement can return a maximum of 1 MB (1024 KB) of data. execute_statement can also be used with extended procedures, and inserts the data returned by the main thread of the extended procedure. Output from threads other than the main thread are not inserted.
Note For SQL Server version 7.0, execute_statement cannot contain an extended stored procedure that returns text or image columns. This behavior is a change from earlier versions of SQL Server.
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 with INSERT. To modify column values in existing rows, use UPDATE. To create a new table and load it with data in one step, use the INTO option of the SELECT statement.
A table variable, in its scope, may be accessed like a regular table. Thus, table variable may be used as the table to which rows are to be added in an INSERT statement. For more information, see table.
A four-part name constructed with the OPENDATASOURCE function as the server-name part may be used as a table source in all places a table name can appear in INSERT statements.
Columns created with the uniqueidentifier data type store specially formatted 16-byte binary values. Unlike with identity columns, SQL Server 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).
When you insert rows, these 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.
This table shows the default operation when SET ANSI_PADDING is 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 consisting 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 the compatibility level for the database is less than 70, the value is converted to a single space. For more information, see sp_dbcmptlevel.
- 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 SQL Server 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 entire statement to be terminated, and no rows are loaded.
- When inserting values into remote SQL Server tables, and not all values for all columns are specified, the user must identify the columns to which the specified values are to be inserted.
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 against remote tables in SQL Server 2000 when the compatibility level is set to 80.
When an INSTEAD-OF trigger is defined on INSERT actions against a table or view, the trigger executes instead of the INSERT statement. Previous versions of SQL Server only support AFTER triggers defined on INSERT and other data modification statements.
When an INSERT statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, SQL Server handles these errors as if SET ARITHABORT is ON. The remainder of the batch is halted, and an error message is returned.
Permissions
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.
Examples
A. Use a simple INSERT
This example creates the table T1 and inserts one row.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
CREATE TABLE T1 ( column_1 int, column_2 varchar(30))
INSERT T1 VALUES (1, 'Row #1')
B. Insert data that is not in the same order as the columns
This example uses column_list and VALUES list to explicitly specify the values that are inserted into each column.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
CREATE TABLE T1 ( column_1 int, column_2 varchar(30))
INSERT T1 (column_2, column_1) VALUES ('Row #1',1)
C. Insert data with fewer values than columns
This example creates a table that has four columns. The INSERT statements insert rows that contain values for some of the columns, but not all of them.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
CREATE TABLE T1
( column_1 int identity,
column_2 varchar(30)
CONSTRAINT default_name DEFAULT ('column default'),
column_3 int NULL,
column_4 varchar(40)
)
INSERT INTO T1 (column_4)
VALUES ('Explicit value')
INSERT INTO T1 (column_2,column_4)
VALUES ('Explicit value', 'Explicit value')
INSERT INTO T1 (column_2,column_3,column_4)
VALUES ('Explicit value',-44,'Explicit value')
SELECT *
FROM T1
D. Load data into a table with 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.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
CREATE TABLE T1 ( column_1 int IDENTITY, column_2 varchar(30))
INSERT T1 VALUES ('Row #1')
INSERT T1 (column_2) VALUES ('Row #2')
SET IDENTITY_INSERT T1 ON
INSERT INTO T1 (column_1,column_2)
VALUES (-99,'Explicit identity value')
SELECT *
FROM T1
E. Load data into a table through a view
The INSERT statement in this example specifies a view name; however, the new row is inserted in the view's underlying table. The order of VALUES list in the INSERT statement must match the column order of the view.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'V1')
DROP VIEW 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)
SELECT *
FROM T1
F. Load data using the DEFAULT VALUES option
The CREATE TABLE statement in this example defines each column with a value that can be used when no explicit value for the column is specified in the INSERT statement. The DEFAULT VALUES option of the INSERT statement is used to add rows without supplying explicit values.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GO
CREATE DEFAULT bound_default AS 'Bound default value'
GO
CREATE TABLE T1
( column_1 int identity,
column_2 varchar(30)
CONSTRAINT default_name DEFAULT ('column default'),
column_3 timestamp,
column_4 varchar(30),
column_5 int NULL)
GO
USE master
EXEC sp_bindefault 'bound_default','T1.column_4'
INSERT INTO T1 DEFAULT VALUES
SELECT *
FROM T1
G. Load data using the SELECT and EXECUTE options
This example demonstrates three different methods for getting data from one table and loading 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 table, authors, and store the result set in the author_sales table. The second INSERT executes a procedure that contains the SELECT statement, and the third INSERT executes the SELECT statement as a literal string.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'author_sales')
DROP TABLE author_sales
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'get_author_sales' AND type = 'P')
DROP PROCEDURE get_author_sales
GO
USE pubs
CREATE TABLE author_sales
( data_source varchar(20),
au_id varchar(11),
au_lname varchar(40),
sales_dollars smallmoney
)
GO
CREATE PROCEDURE get_author_sales
AS
SELECT 'PROCEDURE', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id like '8%'
GROUP BY authors.au_id, authors.au_lname
GO
--INSERT...SELECT example
USE pubs
INSERT author_sales
SELECT 'SELECT', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id LIKE '8%'
GROUP BY authors.au_id, authors.au_lname
--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales
--INSERT...EXECUTE('string') example
INSERT author_sales
EXECUTE
('
SELECT ''EXEC STRING'', authors.au_id, authors.au_lname,
SUM(titles.price * sales.qty)
FROM authors INNER JOIN titleauthor
ON authors.au_id = titleauthor.au_id INNER JOIN titles
ON titleauthor.title_id = titles.title_id INNER JOIN sales
ON titles.title_id = sales.title_id
WHERE authors.au_id like ''8%''
GROUP BY authors.au_id, authors.au_lname
')
--Show results.
SELECT * FROM author_sales
H. Insert data using the TOP clause in a SELECT statement
Because a SELECT statement can be specified in an INSERT statement, the TOP clause can also be used within the SELECT statement. The example inserts the top 10 authors from the authors table into a new table called new_authors.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'new_authors')
DROP TABLE new_authors
GO
USE pubs
CREATE TABLE new_authors
(
au_id id,
au_lname varchar(40),
au_fname varchar(20),
phone char(12),
address varchar(40),
city varchar(20),
state char(2),
zip char(5),
contract bit
)
INSERT INTO new_authors
SELECT TOP 10 *
FROM authors