SELECT (with INTO)

Developer Reference : Data Access Methods : SQL Engine Reference : SQL Syntax Reference : SELECT (with INTO)

SELECT (with INTO)

The SELECT (with INTO) statement allows you to select column values from a specified table to insert into variables or to populate a table with data.

Syntax

SELECT [ ALL | DISTINCT ] [ top-clause ] select-list INTO variable | table-name | temp-table-name [ , variable ]...

FROM table-reference [ , table-reference ]... [ WHERE search-condition ]

[ GROUP BY expression [ , expression ]...[ HAVING search-condition ]] [ UNION [ALL ] query-specification ] [ ORDER BY order-by-expression [ , order-by-expression ]... ]

query-specification ::= ( query-specification )

| SELECT [ ALL | DISTINCT ] [ top-clause ] select-list

FROM table-reference [ , table-reference ]...

[ WHERE search-condition ]

[ GROUP BY expression [ , expression ]...

[ HAVING search-condition ] ]

variable ::= user-defined-name

table-name ::= user-defined-name of a table

temp-table-name ::= user-defined-name of a temporary table

For the remaining syntax definitions, see SELECT.

Remarks

The variables must occur within a stored procedure, a trigger, or a user-defined function.

You can populate a table by using SELECT INTO only if the SELECT INTO statement occurs outside of a user-defined function or trigger. Populating or creating a table with SELECT INTO is not permitted within a user-defined function or trigger.

SELECT INTO is permitted within a stored procedure.

Only a single table can be created and populated with a SELECT INTO statement. A single SELECT INTO statement cannot create and populate multiple tables.

New tables created by SELECT INTO only maintain CASE and NOT NULL constraints from the source tables. Other constraints such as DEFAULT and COLLATE are not maintained. In addition, no indexes are created on the new table.

Examples

See the examples for CREATE (temporary) TABLE for how to use SELECT INTO to populate temporary tables.

The following example assigns into variables :x, :y the values of first_name and last_name in the Person table where first name is Bill.

SELECT first_name, last_name INTO :x, :y from person where first_name = 'Bill'