The SELECT INTO clause creates a new table based on the columns specified in the select list and on the rows chosen in the WHERE clause.
The SELECT INTO clause makes a permanent table only if the select into/bulkcopy database option is set. To see whether this option is set, execute the sp_helpdb system procedure. In this example, execute sp_helpdb against the pubs database:
sp_helpdb pubs
If the select into/bulkcopy option is not set, the report from sp_helpdb indicates this. Only the system administrator or the database owner can set database options.
If the select into/bulkcopy database option is set, you can use the SELECT INTO clause to create a new permanent table (without using a CREATE TABLE statement). If select into/bulkcopy is not set, SELECT INTO can be used to create only temporary tables. (The examples in this chapter assume that select into/bulkcopy is set.)
Caution Because SELECT INTO is not logged, use DUMP DATABASE to back up your database following a SELECT INTO operation. Do not use DUMP TRANSACTION because after a non-logged operation you cannot restore the log with LOAD TRANSACTION.
The new table is based on the columns you specify in the select list, the table(s) you name in the FROM clause, and the rows you choose in the WHERE clause. The name of the new table must be unique in the database and must conform to the rules for identifiers.
A SELECT INTO statement allows you to define a table and put data into it (based on existing definitions and data) without going through the usual data definition process.
The following example shows a SELECT INTO statement and its results. A table called newtable is created, using two of the columns in the four-column table publishers. Because this particular statement includes no WHERE clause, data from all rows (but only two columns, pub_id and pub_name) of publishers is copied into newtable:
SELECT pub_id, pub_name INTO newtable FROM publishers
Note You cannot run SELECT INTO in the pubs database, so you cannot run these examples.
The new table contains the results of the SELECT statement. It becomes part of the database, just like its parent table.
The SELECT INTO clause is useful for creating test tables and copies of existing tables, and for making several smaller tables out of one large table. You can also use SELECT INTO to create a skeleton table with no data by putting a false condition in the WHERE clause. For example:
SELECT * INTO newtable2 FROM publishers WHERE 1 = 2 SELECT * FROM newtable2
pub_id |
pub_name |
city |
state |
------ |
-------- |
---- |
----- |
Although newtable2 is created, no rows are inserted into it because 1 never equals 2.