SELECT… INTO

Local temp tables are very useful for holding a subset of data from a regular table. In order to copy that data from a regular table to a temp table, you can use the SELECT… INTO command. This example makes a copy of the authors table:

select   *
into      #MyAuthors
from      authors

(23   row(s)   affected)

After this command runs, there will be two identical tables. The authors table itself is unaffected by this command: the server reads all the rows in authors and makes a copy. There is now a new, local temp table, #MyAuthors, in tempdb.

---

Warning:  SELECT… INTO can be used to create permanent tables as well, but in order to do so, the target table’s database must have the SELECT INTO/BULKCOPY database option set to TRUE. Tempdb has this option on by default. If you want to set this on, you must own the database and then issue this command:

sp_dboption   ‘pubs’,   ‘select into’,   true

The first argument is the database name, the second argument is the option, and the last is the option’s new value.

---

In addition to creating exact copies, SELECT INTO can also create tables with just a few rows, or just a few columns, from the original. Suppose you needed information for a mailing list of authors. You’re going to be selecting from the table all day, and you don’t want to go through the larger authors table. Instead, you decide to create your own local temporary table, containing the author last name, first name, and address columns. You also want to limit the rows to authors living in California:

select    au_lname,    au_fname,   address,   city,   state,   zip
into       #tempauthors
from       authors
where      state = ‘CA’

(15   row(s)   affected)

This command creates a new, local temporary table in tempdb called #tempauthors. It contains only the six columns mentioned in the SELECT list, and only California authors.

---

Note:  SELECT INTO is a nonlogged operation. This means that the transaction log does not keep a record of the table creation, or know about the rows that were used to create the table. However, the log is used to track normal operations (insert, update, delete) on the new table.

This has good points and bad. On the good side, nonlogged operations are much faster. On the bad side, if the server were to crash in the middle of creating the table, all of the data in the new table would be lost. (Not a big deal, since you can recreate it with a new SELECT INTO, but if it ran for ten hours before crashing…) After a SELECT INTO operation, you cannot perform an incremental (transaction log) backup of your database until you have fully backed it up. Non-logged operations cannot appear in certain statements: SELECT INTO cannot appear in a view (described later today) or inside a transaction (Day 13).

---

SELECT… INTO Versus INSERT… SELECT

You know two ways to copy data from one table to another. Table 10.1 summarizes the differences between SELECT INTO (described earlier) and INSERT… SELECT (described yesterday).

SELECT INTO is also useful when you have permission to select from a base table, but do not have permission to delete from it. If you want your own copy of a table to do with as you please, SELECT INTO can get it for you.

---

Tip:  To copy a table structure, but none of the rows in it, use SELECT INTO with a condition that matches none of the rows:

select *
from   publishers
where  1=2

---