The SQL Procedure

INSERT Statement

Adds rows to a new or existing table or view.

Restriction: You cannot use INSERT INTO on a table accessed via an engine that does not support UPDATE processing.

Featured in: Creating a Table and Inserting Data into It

INSERT INTOtable-name|sas/access-view|proc-sql-view <(column<,column>...)><,user-name>...;

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.
sas/access-view: specifies a SAS/ACCESS view into which you are inserting rows.
proc-sql-view: specifies a PROC SQL view into which you are inserting rows.
sql-expression: See sql-expression .
table-name: specifies a PROC SQL table into which you are inserting rows.
value: is a data value.

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

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

the CONTENTS statement in the DATASETS procedure. See CONTENTS Statement .
the DICTIONARY.INDEXES table. See DICTIONARY tables for more information.

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

Chapter Contents
Previous
Next
Top of Page

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