insert

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

Usage

See also:
  • System and User-Defined Datatypes in Reference Manual: Building Blocks

  • sp_bindefault, sp_bindrule, sp_help, sp_helpartition, sp_unbindefault, sp_unbindrule in Reference Manual: Procedures

  • bcp in the Utility Guide

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.

Note: The 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.

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

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:

InformationValues
Event

41

Audit option

insert

Command or access audited

insert into a table

Information in extrainfo
  • 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

InformationValues
Event

42

Audit option

insert

Command or access audited

insert into a view

Information in extrainfo
  • 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

Related reference
alter table
create default
create index
create rule
create table
create trigger
dbcc
delete
select
update