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 )
FROM table-reference [ , table-reference ]...
[ WHERE search-condition ]
[
GROUP BY expression [ ,
expression ]...
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