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'
See Also
CREATE FUNCTION
CREATE PROCEDURE
CREATE (temporary) TABLE
CREATE TABLE