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