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 specified 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.
The select and select into clauses, as well as the delete and update clauses, enable TOP functionality. The TOP option is an unsigned integer that allows you to limit the number of rows inserted in the target table. It implements compatibility with other platforms. See Reference Manual: Commands.
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. This example uses a page size of 8K.
sp_helpdb pubs2
name db_size owner dbid created status --------- ---------- --------- ---- -------------- -------------- pubs2 20.0 MB sa 4 Apr 25, 2005 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data device_fragments size usage created free kbytes ------------------- ------------- ------------- ---------- ------------ master 10.0MB data and log Apr 13 2005 1792
pubs_2_dev 10.0MB data and log Apr 13 2005 9888 device segment ---------------------- ---------------------- master default master logsegment master system pubs_2_dev default pubs_2_dev logsegment pubs_2_dev system pubs_2_dev seg1 pubs_2_dev seg2
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 12, “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 specify 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. newtable looks like this:
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.
