The SQL Procedure

INSERT Statement

Adds rows to a new or existing table or view.

Restriction:	You cannot use INSERT INTO on a table that is accessed with an engine that does not support UPDATE processing.
Featured in:	Creating a Table and Inserting Data into It

INSERT INTO table-name|sas/access-view|proc-sql-view <(column<, ... column>)>

SET column=sql-expression
<, ... column=sql-expression>

<SET column=sql-expression
<, ... column=sql-expression>>;

INSERT INTO table-name|sas/access-view|proc-sql-view <(column<, ... column>)>

VALUES (value <, ... value>)
<... VALUES (value <, ... value>)>;

INSERT INTO table-name|sas/access-view|proc-sql-view
<(column<, ...column>)> query-expression;

Arguments

column

specifies the column into which you are inserting rows.

proc-sql-view

specifies a PROC SQL view into which you are inserting rows. proc-sql-view can be a one-level name, a two-level libref.view name, or a physical pathname that is enclosed in single quotation marks.

query-expression

See query-expression.

sas/access-view

specifies a SAS/ACCESS view into which you are inserting rows.

sql-expression

See sql-expression.

Restriction:

You cannot use a logical operator (AND, OR, or NOT) in an expression in a SET clause.

table-name

specifies a PROC SQL table into which you are inserting rows. table-name can be a one-level name, a two-level libref.table name, or a physical pathname that is enclosed in single quotation marks.

value

is a data value.

CAUTION:

Recursive table references can cause data integrity problems.

While it is possible to recursively reference the target table of an INSERT statement, doing so can cause data integrity problems and incorrect results. Constructions such as the following should be avoided:

proc sql;
   insert into a
      select var1, var2
      from a
      where var1 > 0;

[cautionend]

Methods for Inserting Values

	The first form of the INSERT statement uses the SET clause, which specifies or alters the values of a column. You can use more than one SET clause per INSERT statement, and each SET clause can set the values in more than one column. Multiple SET clauses are not separated by commas. If you specify an optional list of columns, then you can set a value only for a column that is specified in the list of columns to be inserted.
	The second form of the INSERT statement uses the VALUES clause. This clause can be used to insert lists of values into a table. You can either give a value for each column in the table or give values just for the columns specified in the list of column names. One row is inserted for each VALUES clause. Multiple VALUES clauses are not separated by commas. The order of the values in the VALUES clause matches the order of the column names in the INSERT column list or, if no list was specified, the order of the columns in the table.
	The third form of the INSERT statement inserts the results of a query-expression into a table. The order of the values in the query-expression matches the order of the column names in the INSERT column list or, if no list was specified, the order of the columns in the table.

Note: If the INSERT statement includes an optional list of column names, then only those columns are given values by the statement. Columns that are in the table but not listed are given missing values. [cautionend]

Inserting Rows through Views

You can insert one or more rows into a table through a view, with some restrictions. See Updating PROC SQL and SAS/ACCESS Views in the SAS 9.2 SQL Procedure User's Guide.

Adding Values to an Indexed Column

If an index is defined on a column and you insert a new row into the table, then that value is added to the index. You can display information about indexes with

the CONTENTS statement in the DATASETS procedure. See the CONTENTS Statement.
the DICTIONARY.INDEXES table. See Accessing SAS System Information Using DICTIONARY Tables in the SAS 9.2 SQL Procedure User's Guide SAS 9.2 SQL Procedure User's Guide for more information.

For more information on creating and using indexes, see the CREATE INDEX Statement.

Top of Page