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 Chapter 16, “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


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

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

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

    • 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

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