insert

Description

Adds new rows to a table or view.

Syntax

insert [into] [database.[owner.]]{table_name|view_name}
	[(column_list)] 
	{values (expression [, expression]...)
		|select_statement [plan "abstract plan"]}

Parameters

Examples

Example 1

insert titles 
values ("BU2222", "Faster!", "business", "1389", 
    null, null, null, "ok", "06/17/87", 0)

Example 2

insert titles
 (title_id, title, type, pub_id, notes, pubdate,
    contract) 
values ('BU1237', 'Get Going!', 'business',
    '1389', 'great', '06/18/86', 1)

Example 3

insert newauthors 
    select * 
    from authors 
    where city = "San Francisco"

Example 4

insert test 
    select * 
    from test 
    where city = "San Francisco"

Example 5

insert table1 (col1, col2, col3, col4) 
    values (10, 4, default, 34)

Usage

• 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).

• 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 , ortext, or an image column simply allocates space for a text pointer. 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 for a numeric. For integer identities, it is the maximum permissible value of its type (such as 255 for tinyint, 32767 for smallint). 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.

  stores_cal30 does not have a with check option clause of its own. This means 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 Chapter 6, “Controlling Physical Data Placement,” in Performance and Tuning Guide: Basics.

Standards

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.

Permissions

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

Auditing

Values in event and extrainfo columns of sysaudits are:

See also