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

into

is optional.

table_name | view_name

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.

column_list

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.

values

introduces a list of expressions.

expression

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 “Chapter 1, “System and User-Defined Datatypes” in Reference Manual: Building Blocks for more information about data entry rules.

select_statement

is a standard select statement used to retrieve the values to be inserted.

plan "abstract plan"

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 “Creating and Using Abstract Plans” in the Performance and Tuning Guide: Optimizer and Abstract Plans for more information.

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


The column list

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


Validating column values


Treatment of blanks


Inserting into text, unitext, and image columns

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.


insert triggers

You can define a trigger that takes a specified action when an insert command is issued on a specified table.


Using insert when CIS is enabled

You can send an insert as a language event or as a parameterized dynamic statement to remote servers.


Inserting rows selected from another table

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.


Transactions and insert

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.


Inserting values into IDENTITY columns


Inserting data through views


Partitoning tables for improved insert performance

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 “Controlling Physical Data Placement” in Performance and Tuning Guide: Basics.

Standards

ANSI SQL – Compliance level: Entry-level compliant.

The following are Transact-SQL extensions:

NoteThe FIPS flagger does not detect insertions through a view that contains a join.

Permissions

The permission checks for insert differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must be the table owner or a user with insert permissionon the table. Only the table owner or user with insert and identity_insert permissions can insert for the table's IDENTITY column.

Granular permissions disabled

With granular permissions disabled, you must be the table owner or a user with sa_role.

insert permission defaults to the table owner.

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:

Event

Audit option

Command or access audited

Information in extrainfo

41

insert

insert into a table

  • Roles – current active roles

  • Keywords or options– if:

    • insertINSERT

    • select intoINSERT INTO followed by the fully qualified object name

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

42

insert

insert into a view

  • Roles – current active roles

  • Keywords or optionsINSERT

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if a set proxy is in effect

See also

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

Utilities bcp