INSERT Statement

For SQL Server 6.5 information, see INSERT Statement in What's New for SQL Server 6.5.

Adds a new row to a table or a view.

Syntax

INSERT [INTO]
    {table_name | view_name} [(column_list)]
{DEFAULT VALUES | values_list | select_statement}

where

INTO
Is an optional keyword.
table_name | view_name =
[[database_name.]owner.]{table_name | view_name}

Specifies the name of the table or view used in the INSERT statement. If the table or view is not in the current database, use a fully qualified table_name or view_name (database_name.owner.object_name).

column_list
Lists one or more columns to which data is to be added. The columns can be listed in any order, but the incoming data (whether in a values_clause or a select_statement) must be in the same order as the columns.

The column_list is necessary only when some, but not all, columns in the table are to receive data. You can leave out items in the column_list and values_clause as long as the omitted columns are defined to allow null values or the columns have an associated default (a default or a DEFAULT constraint).

Enclose the column_list in parentheses. If column_list is not specified, all columns in the receiving table (in CREATE TABLE order) are assumed to be included.

DEFAULT VALUES
Inserts the default values for all columns. If the column has the IDENTITY property or the timestamp datatype, the next appropriate value will be inserted. If a default for the column does not exist and the column allows NULLs, NULL will be inserted. If any column of the table does not have a default or does not allow NULL, an error will be returned and the INSERT statement rejected.
values_list =
VALUES (DEFAULT | constant_expression
[, DEFAULT | constant_expression]...)
VALUES
Is a required keyword used to introduce the list of values for each column in the column_list or table.
DEFAULT
Inserts the default value for that column. For a column defined with the timestamp datatype, the next value will be inserted. If a default does not exist for the column and the column allows NULLs, NULL will be inserted.

DEFAULT is not valid for an identity column. Columns with the IDENTITY property should not be explicitly listed in the column_list or values_clause; the value for an identity column should not be explicitly entered. For more information, see the CREATE TABLE statement.

constant_expression
Contains an expression that will evaluate to a constant. The list of values must be enclosed in parentheses and must match the explicit or implicit columns list. Enclose non-numeric constants in quotation marks.
select_statement
Is a standard SELECT statement used to retrieve the values to be inserted from an existing table. For details, see the SELECT statement.

Remarks

INSERT adds new rows only. To modify column values in existing rows, use UPDATE.

When inserting rows, these rules apply:

When you specify values for only some of the columns in the column_list, one of three things can happen to the columns that have no values:

Permission

INSERT permission defaults to the table owner, who can transfer it to other users.

Examples

A.    INSERT Specifying All Column Values

This example shows an INSERT into the titles table in the pubs database. All column values are specified in the values_clause.

INSERT titles
    VALUES('BU2222', 'Faster!', 'business', '1389',
        NULL, NULL, NULL, NULL, 'ok', '06/17/87')
B.    INSERT with Column Titles

This example shows an insert into the titles table in the pubs database. Only the values for the columns listed in the column_list are shown in the values_list.

INSERT titles(title_id, title, type, pub_id, notes, pubdate)
    VALUES ('BU1237', 'Get Going!', 'business', '1389',
        'great', '06/18/86')
C.    INSERT with Nested SELECT

This example inserts all rows from the authors table (for authors only in San Francisco) into the newauthors table.

INSERT INTO newauthors
    SELECT *
        FROM authors
            WHERE city = 'San Francisco'
D.    INSERT with DEFAULT VALUES

This example shows the use (and failure) of the DEFAULT VALUES option for the INSERT statement.

INSERT publishers DEFAULT VALUES
go
Msg 233, Level 16, State 2
The column pub_id in table publishers may not be null.
E.    INSERT with the DEFAULT Keyword

This example shows the use of DEFAULT as a placeholder to insert the DEFAULT value for a column(s).

INSERT employee
    VALUES ('KLT91469F', 'Katrina', 'L', 'Thompson', DEFAULT, DEFAULT,
         DEFAULT, '01/14/95')

See Also

CREATE TABLE statement sp_bindefault system stored procedure
CREATE TRIGGER statement sp_bindrule system stored procedure
Datatypes sp_unbindefault system stored procedure
DELETE statement sp_unbindrule system stored procedure
SELECT statement UPDATE statement