Creating new tables from query results: select into

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, and is documented in the select command in the Reference Manual.

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
pub­s_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.

Unlike a view that displays a portion of a table, a table created with select into is a separate, independent entity. See Chapter 11, “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.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF