LearnNowOnline

Blog

SQL Server 2012: Creating a New Table with SELECT INTO in T-SQL

Excerpt by Don Kiely | May 07, 2013

The SQL standard draws a distinction between Data Manipulation Language (DML) and Data Definition Language (DDL). DML statements manipulate and maintain data, while DDL statements change the structure and properties of database objects. SELECT statements are considered to be part of DML, even though you might not think of selecting data as a form of "manipulation."

When you retrieve data with a SELECT statement, SQL returns a result set. However, any actions you take on that data (besides just looking at it) involve data manipulation queries that use one of the following three T-SQL statements: INSERT, UPDATE, or DELETE. SQL queries that use these statements are sometimes known as action queries.

SQL Server was designed to handle data modification extremely efficiently via T-SQL DML statements. This article takes a look at the SELECT INTO statement for creating new tables using T-SQL.

SELECT INTO

The SELECT INTO statement creates a new table with the columns necessary to contain all the values that the SELECT statement returns. The following query creates a new table called Produce, which contains all of the rows that match the produce category in the Products table:

SELECT dbo.Products.* INTO dbo.Produce

FROM dbo.Products

WHERE CategoryID = 7;

If you run the statement again, you'll receive an error that the Produce table already exists.

NOTE: When you create a new table with SELECT INTO, the new table schema will match the original schema, including identity columns. The Produce table will have a ProductID that is an identity column. The nullability of columns is also copied from the source, but indexes, constraints, and triggers are not copied.

Temporary Tables

Although the SELECT INTO syntax allows you to create tables in a database, you probably won't want to use this technique very often in applications. When you allow users to create tables at will, problems can occur with backups, security, and database clutter. But sometimes you'll want to create temporary, or temp, tables as a way to store data that's necessary for processing but is not required afterward.

You can create temp tables so that only the connection that creates them can access them. Once that connection ends, SQL Server automatically destroys the tables, which eliminates all of the problems with persistent tables. Temp tables are stored in the tempdb database, and are identifiable by the first character of their name: the pound symbol (#).

NOTE: Temp tables created in stored procedures are destroyed when the stored procedure terminates.

The following SELECT INTO query creates a temporary table named #Produce:

SELECT dbo.Products.* INTO #Produce

FROM dbo.Products

WHERE CategoryID = 7;

Namespace conflicts won't occur if multiple users create a #Produce table, since every connection can have its own #Produce in tempdb. Each #Produce table in tempdb has a suffix with an underscore and the connection information to keep them separate. To select data from the temp table, use the following query:

>SELECT * FROM #Produce;

Another way to create temp tables is to use the standard CREATE TABLE syntax, as shown in the following DDL statement:

CREATE TABLE #Beverages(

ProductID int NOT NULL,

ProductName nvarchar(40),

SupplierID int NULL,

CategoryID int NULL,

QuantityPerUnit nvarchar(20),

UnitPrice money NULL,

UnitsInStock smallint NULL,

UnitsOnOrder smallint NULL,

ReorderLevel smallint NULL,

Discontinued bit NOT NULL

);

The following query inserts data into the temp table. Note that the data types you explicitly define in the temp table must match the data types in the table you are selecting data from:

INSERT INTO #Beverages

SELECT

ProductID, ProductName, SupplierID,

CategoryID, QuantityPerUnit, UnitPrice,

UnitsInStock, UnitsOnOrder, ReorderLevel,

Discontinued

FROM dbo.Products

WHERE Products.CategoryID = 1;