Adds new rows to a table or view.


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



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.


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.


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


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


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


The column list

Validating column values

Treatment of blanks

Inserting into text, unitext, and image columns

insert triggers

Using insert when Component Integration Services is enabled

Inserting rows selected from another table

Transactions and insert

Inserting values into IDENTITY columns

Inserting data through views

Partitoning tables for improved insert performance


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.



Values in event and extrainfo columns of sysaudits are:


Audit option

Command or access audited

Information in extrainfo



insert into a table

  • Roles – Current active roles

  • Keywords or options

    • If insertINSERT

    • If 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 in effect



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