Home

	Expand Search

Transact-SQL User's Guide

Chapter 7 Creating Databases and Tables

Creating new tables from query results: select into

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 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 9, "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.

Checking for errors

select into is a two-step operation. The first step creates the new table and the second step inserts the specified rows into the table.

Because select into operations are not logged, they cannot be issued within user-defined transactions and cannot be rolled back.

If a select into statement fails after creating a new table, Adaptive Server does not automatically drop the table or deallocate its first data page. This means that any rows inserted on the first page before the error occurred remain on the page. Check the value of the @@error global variable after a select into statement to be sure that no error occurred.

If an error occurs from a select into operation, drop table to remove the new table, then reissue the select into statement.

Using select into with IDENTITY columns

This section describes special rules for using the select into command with tables containing IDENTITY columns.

To select an existing IDENTITY column into a new table, include the column name (or the syb_identity keyword) in the select statement's column_list:

select column_list 
     into table_name
     from table_name

The following example creates a new table, stores_cal_pay30, based on columns from the stores_cal table:

select record_id, stor_id, stor_name
into stores_cal_pay30
from stores_cal
where payterms = "Net 30"

The new column inherits the IDENTITY property, unless any of the following conditions is true:

• The IDENTITY column is selected more than once.

• The IDENTITY column is selected as part of an expression.

• The select statement contains a group by clause, aggregate function, union operator, or join.

A table cannot have more than one IDENTITY column. If an IDENTITY column is selected more than once, it is defined as NOT NULL in the new table. It does not inherit the IDENTITY property.

In the following example, the record_id column, which is selected once by name and once by the syb_identity keyword, is defined as NOT NULL in stores_cal_pay60:

select syb_identity, record_id, stor_id, stor_name
into stores_cal_pay60
from stores_cal
where payterms = "Net 60"

To define a new IDENTITY column in a select into statement, add the column definition before the into clause. The definition includes the column's precision but not its scale:

select column_list
     identity_column_name = identity(precision)
     into table_name
     from table_name

The following example creates a new table, new_discounts, from the discounts table and adds a new IDENTITY column, id_col:

select *, id_col=identity(5)
into new_discounts
from discounts

If the column_list includes an existing IDENTITY column, and you add a description of a new IDENTITY column, the select into statement fails.

IDENTITY column values are generated by Adaptive Server. New columns that are based on IDENTITY columns, but whose values must be computed rather than generated, cannot inherit the IDENTITY property.

If a table's select statement includes an IDENTITY column as part of an expression, the resulting column value must be computed. The new column is created as NULL if any column in the expression allows a NULL value. Otherwise, it is NOT NULL.

In the following example, the new_id column, which is computed by adding 1000 to the value of record_id, is created NOT NULL:

select new_id = record_id + 1000, stor_name
into new_stores
from stores_cal

Column values are also computed if the select statement contains a group by clause or aggregate function. If the IDENTITY column is the argument of the aggregate function, the resulting column is created NULL. Otherwise, it is NOT NULL.

The value of the IDENTITY column uniquely identifies each row in a table. However, if a table's select statement contains a union or join, individual rows can appear multiple times in the result set. An IDENTITY column that is selected into a table with a union or join does not retain the IDENTITY property. If the table contains the union of the IDENTITY column and a NULL column, the new column is defined as NULL. Otherwise, it is NOT NULL.

For more information, see "Using IDENTITY columns" and "Updating IDENTITY columns". See also select in the Reference Manual.