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.
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
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.
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
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,
SupplierID int NULL,
CategoryID int NULL,
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
ProductID, ProductName, SupplierID,
CategoryID, QuantityPerUnit, UnitPrice,
UnitsInStock, UnitsOnOrder, ReorderLevel,
WHERE Products.CategoryID = 1;This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.