The select into command lets you create a new table based on the columns specified in the select statement’s select list and the rows chosen in the where clause. The into clause is useful for creating test tables, new tables as copies of existing tables, and for making several smaller tables out of one large table. You can use select into on a permanent table only if the select into/bulkcopy/pllsort database option is set to on. A System Administrator can turn on this option using sp_dboption. Use sp_helpdb to see if this option is on.
Here is what sp_helpdb and its results look like when the select into/bulkcopy/pllsort database option is set to on:
sp_helpdb pubs2
name db_size owner dbid created status
--------- ------- ------ ----- ----------- ------------
pubs 2 MB sa 5 Jun 5 1997 select into
/bulkcopy/pllsort
(1 row affected)
device size usage
----------------- --------- --------------
master 2 MB data and log
(1 row affected)
sp_helpdb output indicates whether the option is set to on or off. Only the System Administrator or the Database Owner can set the database options.
If the select into/bulkcopy/pllsort database option is on, you can use the select into clause to build a new permanent table without using a create table statement. You can select into a temporary table, even if the select into/bulkcopy/pllsort option is not on.
Because select
into is a minimally logged operation, use dump
database to back up your database following a select
into. You cannot dump the transaction log following a
minimally logged operation.
Unlike a view that displays a portion of a table, a table created with select into is a separate, independent entity. See Chapter 10, “Views: Limiting Access to Data,” for more information.
The new table is based on the columns you specify in the select list, the tables 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 statement with an into clause 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 statement includes no where clause, data from all the rows (but only the two specified columns) of publishers is copied into newtable.
select pub_id, pub_name into newtable from publishers
(3 rows affected)
“3 rows affected” refers to the three rows inserted into newtable. Here’s what newtable looks like:
select * from newtable
pub_id pub_name ------ ------------------------------------ 0736 New Age Books 0877 Binnet & Hardley 1389 Algodata Infosystems
(3 rows affected)
The new table contains the results of the select statement. It becomes part of the database, just like its parent table.
You can 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
(0 rows affected)
select * from newtable2
pub_id pub_name city state ------ -------------- -------- ----- (0 rows affected)
No rows are inserted into the new table, because 1 never equals 2.
You can also use select into with aggregate functions to create tables with summary data:
select type, "Total_amount" = sum(advance) into #whatspent from titles group by type
(6 rows affected)
select * from #whatspent
type Total_amount ------------ ------------------------ UNDECIDED NULL business 25,125.00 mod_cook 15,000.00 popular_comp 15,000.00 psychology 21,275.00 trad_cook 19,000.00
(6 rows affected)
Always supply a name for any column in the select into result table that results from an aggregate function or any other expression. Examples are:
Arithmetic aggregates, for example, amount * 2
Concatenation, for example, lname + fname
Functions, for example, lower(lname)
Here is an example of using concatenation:
select au_id,
"Full_Name" = au_fname + ’ ’ + au_lname
into #g_authortemp
from authors
where au_lname like "G%"
(3 rows affected)
select * from #g_authortemp
au_id Full_Name ----------- ------------------------- 213-46-8915 Marjorie Green 472-27-2349 Burt Gringlesby 527-72-3246 Morningstar Greene (3 rows affected)
Because functions allow null values, any column in the table that results from a function other than convert() or isnull() allows null values.
