Adds new rows to a table or view.
insert [into] [database.[owner.]]{table_name|view_name}
[(column_list)]
{values (expression [, expression]...)
|select_statement [plan "abstract plan"] }
is optional.
is the name of the table or view from which you want to remove rows. Specify the database name if the table or view is in another database, and specify the owner’s name if more than one table or view of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.
is a list of one or more columns to which data is to be added. Enclose the list in parentheses. The columns can be listed in any order, but the incoming data (whether in a values clause or a select clause) must be in the same order. If a column has the IDENTITY property, you can substitute the syb_identity keyword for the actual column name.
The column list is necessary when some, but not all, of the columns in the table are to receive data. If no column list is given, Adaptive Server assumes that the insert affects all columns in the receiving table (in create table order).
See “The column list” for more information.
is a keyword that introduces a list of expressions.
specifies constant expressions, variables, parameters, or null values for the indicated columns. Enclose character and datetime constants in single or double quotes.
You cannot use a subquery as an expression.
The values list:
Must be enclosed in parentheses
Must match the explicit or implicit column list
Can use “default” as a value
See “Datatypes” for more information about data entry rules.
is a standard select statement used to retrieve the values to be inserted.
specifies the abstract plan to use to optimize the query. It can be a full or partial plan, specified in the abstract plan language. Plans can only be specified for insert...select statements. See Chapter 30, “Creating and Using Abstract Plans,” in the Performance and Tuning Guide for more information.
insert titles
values("BU2222", "Faster!", "business", "1389",
null, null, null, "ok", "06/17/87", 0)
insert titles
(title_id, title, type, pub_id, notes, pubdate,
contract)
values ('BU1237', 'Get Going!', 'business',
'1389', 'great', '06/18/86', 1)
insert newauthors select * from authors where city = "San Francisco"
insert test select * from test where city = "San Francisco"
insert table1 (col1, col2, col3, col4) values (10, 4, default, 34)
Use insert only to add new rows. Use update to modify column values in a row you have already inserted.
The column list determines the order in which values are entered. For example, suppose that you have a table called newpublishers that is identical in structure and content to the publishers table in pubs2. In the example below, the columns in the column list of the newpublishers table match the columns of the select list in the publishers table.
insert newpublishers (pub_id, pub_name) select pub_id, pub_name from publishers where pub_name="New Age Data"
The pub_id and pub_name for “New Age Data” are stored in the pub_id and pub_name columns of newpublishers.
In the next example, the order of the columns in the column list of the newpublishers table does not match the order of the columns of the select list of the publishers table.
insert newpublishers (pub_id, pub_name) select pub_name, pub_id from publishers where pub_name="New Age Data"
The result is that the pub_id for “New Age Data” is stored in the pub_name column of the newpublishers table, and the pub_name for “New Age Data” is stored in the pub_id column of the newpublishers table.
You can omit items from the column and values lists as long as the omitted columns allow null values (see Example 2).
insert interacts with the ignore_dup_key, ignore_dup_row, and allow_dup_row options, which are set with the create index command. See create index for more information.
A rule or check constraint can restrict the domain of legal values that can be entered into a column. Rules are created with the create rule command and bound with sp_bindrule. check constraints are declared with create table.
A default can supply a value if you do not explicitly enter one. Defaults are created with the create default command and bound with sp_bindefault, or they are declared with create table.
If an insert statement violates domain or integrity rules (see create rule and create trigger), or if it is the wrong datatype (see create table and Chapter 1, “System and User-Defined Datatypes” in Reference Manual: Building Blocks), the statement fails, and Adaptive Server displays an error message.
Inserting an empty string ("") into a variable character type or text column inserts a single space. char columns are padded to the defined length.
All trailing spaces are removed from data that is inserted into varchar and univarchar columns, except in the case of a string that contains only spaces. Strings that contain only spaces are truncated to a single space. Strings that are longer than the specified length of a char, nchar, unichar, univarchar, varchar, or nvarchar column are silently truncated unless the string_rtruncation option is set to on.
An insert of a NULL into a text or an image column does not create a valid text pointer, nor does it a text page as would otherwise occur. Use update to get a valid text pointer for that column.
You can define a trigger that takes a specified action when an insert command is issued on a specified table.
You can send an insert as a language event or as a parameterized dynamic statement to remote servers.
You can select rows from a table and insert them into the same table in a single statement (see Example 4).
To insert data with select from a table that has null values in some fields into a table that does not allow null values, provide a substitute value for any NULL entries in the original table. For example, to insert data into an advances table that does not allow null values, substitute 0 for the NULL fields:
insert advances select pub_id, isnull(advance, 0) from titles
Without the isnull function, this command inserts all the rows with non-null values into the advances table, which produces error messages for all the rows where the advance column in the titles table contained NULL.
If you cannot make this kind of substitution for your data, you cannot insert data containing null values into the columns that have a NOT NULL specification.
Two tables can be identically structured, and yet be different as to whether null values are permitted in some fields. Use sp_help to see the null types of the columns in your table.
When you set chained transaction mode, Adaptive Server implicitly begins a transaction with the insert statement if no transaction is currently active. To complete any inserts, you must commit the transaction, or roll back the changes. For example:
insert stores (stor_id, stor_name, city, state)
values ('999', 'Books-R-Us', 'Fremont', 'AZ')
if exists (select t1.city
from stores t1, stores t2
where t1.city = t2.city
and t1.state = t2.state
and t1.stor_id < t2.stor_id)
rollback transaction
else
commit transaction
In chained transaction mode, this batch begins a transaction and inserts a new row into the stores table. If it inserts a row containing the same city and state information as another store in the table, it rolls back the changes to stores and ends the transaction. Otherwise, it commits the insertions and ends the transaction. For more information about chained transaction mode, see the Transact-SQL User’s Guide.
When inserting a row into a table, do not include the name of the IDENTITY column in the column list or its value in the values list. If the table consists of only one column, an IDENTITY column, omit the column list and leave the values list empty as follows:
insert id_table values()
The first time you insert a row into a table, Adaptive Server assigns the IDENTITY column a value of 1. Each new row gets a column value that is one higher than the last. This value takes precedence over any defaults declared for the column in the create table or alter table statement or defaults bound to the column with sp_bindefault.
Server failures can create gaps in IDENTITY column values. The maximum size of the gap depends on the setting of the identity burning set factor configuration parameter. Gaps can also result from manual insertion of data into the IDENTITY column, deletion of rows, and transaction rollbacks.
Only the table owner, Database Owner, or System Administrator can explicitly insert a value into an IDENTITY column after setting identity_insert table_name on for the column’s base table. A user can set identity_insert table_name on for one table at a time in a database. When identity_insert is on, each insert statement must include a column list and must specify an explicit value for the IDENTITY column.
Inserting a value into the IDENTITY column allows you to specify a seed value for the column or to restore a row that was deleted in error. Unless you have created a unique index on the IDENTITY column, Adaptive Server does not verify the uniqueness of the value; you can insert any positive integer.
To insert an explicit value into an IDENTITY column, the table owner, Database Owner, or System Administrator must set identity_insert table_name on for the column’s base table, not for the view through which it is being inserted.
The maximum value that can be inserted into an IDENTITY column is 10 precision - 1. Once an IDENTITY column reaches this value, any additional insert statements return an error that aborts the current transaction.
When this happens, use the create table statement to create a new table that is identical to the old one, but that has a larger precision for the IDENTITY column. Once you have created the new table, use either the insert statement or the bcp utility to copy the data from the old table to the new one.
Use the @@identity global variable to retrieve the last value that you inserted into an IDENTITY column. If the last insert or select into statement affected a table with no IDENTITY column, @@identity returns the value 0.
An IDENTITY column selected into a result table observes the following rules with regard to inheritance of the IDENTITY property:
If an IDENTITY column is selected more than once, it is defined as NOT NULL in the new table. It does not inherit the IDENTITY property.
If an IDENTITY column is selected as part of an expression, the resulting column does not inherit the IDENTITY property. It is created as NULL if any column in the expression allows nulls; otherwise, it is created as NOT NULL.
If the select statement contains a group by clause or aggregate function, the resulting column does not inherit the IDENTITY property. Columns that include an aggregate of the IDENTITY column are created NULL; others are created NOT NULL.
An IDENTITY column that is selected into a table with a union or join does not retain the IDENTITY property. If the table contains the union of the IDENTITY column and a NULL column, the new column is defined as NULL; otherwise, it is defined as NOT NULL.
If a view is created with check option, each row that is inserted through the view must meet the selection criteria of the view.
For example, the stores_cal view includes all rows of the stores table for which state has a value of “CA”:
create view stores_cal as select * from stores where state = "CA" with check option
The with check option clause checks each insert statement against the view’s selection criteria. Rows for which state has a value other than “CA” are rejected.
If a view is created with check option, all views derived from the base view must satisfy the view’s selection criteria. Each new row inserted through a derived view must be visible through the base view.
Consider the view stores_cal30, which is derived from stores_cal. The new view includes information about stores in California with payment terms of “Net 30”:
create view stores_cal30 as select * from stores_cal where payterms = "Net 30"
Because stores_cal was created with check option, all rows inserted or updated through stores_cal30 must be visible through stores_cal. Any row with a state value other than “CA” is rejected.
Notice that stores_cal30 does not have a with check option clause of its own. This means that you can insert or update a row with a payterms value other than “Net 30” through stores_cal30. The following update statement would be successful, even though the row would no longer be visible through stores_cal30:
update stores_cal30 set payterms = "Net 60" where stor_id = "7067"
insert statements are not allowed on join views created with check option.
If you insert or update a row through a join view, all affected columns must belong to the same base table.
An unpartitioned table with no clustered index consists of a single doubly linked chain of database pages, so each insertion into the table uses the last page of the chain. Adaptive Server holds an exclusive lock on the last page while it inserts the rows, blocking other concurrent transactions from inserting data into the table.
Partitioning a table with the partition clause of the alter table command creates additional page chains. Each chain has its own last page, which can be used for concurrent insert operations. This improves insert performance by reducing page contention. If the table is spread over multiple physical devices, partitioning also improves insert performance by reducing I/O contention while the server flushes data from cache to disk. For more information about partitioning tables for insert performance, see the Performance and Tuning Guide.
ANSI SQL – Compliance level: Entry-level compliant.
The following are Transact-SQL extensions:
A union operator in the select portion of an insert statement.
Qualification of a table or column name by a database name.
Insertion through a view that contains a join.
The
FIPS flagger does not detect insertions through a view that contains a
join.
insert permission defaults to the table or view owner, who can transfer it to other users.
insert permission for a table’s IDENTITY column is limited to the table owner, Database Owner, and System Administrator.
Commands alter table, create default, create index, create rule, create table, create trigger, dbcc, delete, select, update
Datatypes Chapter 1, “System and User-Defined Datatypes” of Reference Manual: Building Blocks.
System procedures sp_bindefault, sp_bindrule, sp_help, sp_helpartition, sp_unbindefault, sp_unbindrule
