Selection of data into another table
The select into statement retrieves data from one table and inserts it into another. The select into command does not delete the existing record.
Syntax
The following syntax shows how to use the select into statement.
select column_or_wildcard [ , column ... ]
into destination_database_name.destination_table_name
from source_database_name.source_table_name
[ where conditional_test ] ;The columns selected from the source table are inserted into the destination table in order, regardless of the column names and structure of the destination table. Omitting the optional where condition selects all the records from the source table.
If you use a wildcard, all the columns from the source table are selected and inserted in order into the destination table. Any null columns in the source table are skipped in both the source and destination tables, for example, if the fourth column of the source table is null, the fourth column in the destination table is skipped.
If you specify a list of columns to select from the source table, they are inserted into the destination table in the order in which they are specified (even if the order in which they are specified is not the order in which they exist in the source table).
Example 1
The following example selects the values
of the Age and Gender columns of the staff.managers table
and inserts the values into the first two columns of the staff.employees table.
select Age, Gender into staff.employees from staff.managers;Example 2
The following example selects EmployeeID and Name from
any record in the staff.employees table for which Name="Carl" and
inserts the values into the first two columns of the staff.managers table.
|phoenix:1.> select EmployeeID, Name
|phoenix:2.> into staff.managers from staff.employees
|phoenix:3.> where Name="Carl";
|phoenix:4.> go