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.
INSERT [INTO]
{table_name | view_name} [(column_list)]
{DEFAULT VALUES | values_list | select_statement}
where
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).
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 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.
INSERT adds new rows only. To modify column values in existing rows, use UPDATE.
When inserting rows, these rules apply:
All trailing spaces are removed from data inserted into varchar columns, except in strings that contain only spaces. These strings are truncated to a single space.
Constraints are defined with either the CREATE TABLE or ALTER TABLE statement. Defaults are created with the CREATE DEFAULT statement and rules are created with the CREATE RULE statement.
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:
INSERT permission defaults to the table owner, who can transfer it to other users.
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')
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')
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'
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.
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')