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.
See: Creating a Table and Inserting Data into It
Syntax
Required Arguments
Details
Methods for Inserting Values
Inserting Rows through Views
Adding Values to an Indexed Column

Syntax

INSERT INTO table-name|sas/access-view|proc-sql-view <(column<, … column>)>
SET column=sql-expression <, … column=sql-expression>
< 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;

Required 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.
Although 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;

Details

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.

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, then that value is added to the index. You can display information about indexes with
For more information about creating and using indexes, see the CREATE INDEX Statement.
Copyright © SAS Institute Inc. All rights reserved.