By: Koen Verbeeck | Updated: 2022-01-10 | Comments (3) | Related: > TSQL
Problem
With the SELECT INTO statement, you can quickly create a Microsoft SQL Server table using the result set of your SELECT statement. In this tutorial, we'll demonstrate some common scenarios with examples.
Solution
There are a couple of methods to create a new table in SQL Server. You can use the table designer of SQL Server Management Studio (SSMS) or you can write a CREATE TABLE statement using T-SQL. With the SELECT … INTO construct, we have a third option available. With some short examples, you'll discover how you can use this construct to quickly create new tables and copy data.
For the remainder of the tutorial, we assume you have the necessary permissions to actually create tables in a database.
Creating a Table Using a Result Set with Transact-SQL
The basic concept behind SELECT … INTO is that you create a new table using the result set of a SELECT statement. So, you can take any SELECT statement, add the INTO clause and you can create a new table and insert data into it at the same time! Let's illustrate using the Adventure Works sample database.
The following SELECT statements retrieves data from the existing table called Person:
SELECT
[BusinessEntityID] -- Column List
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
FROM [AdventureWorks2017].[Person].[Person];
To dump the data into a new table, we add the INTO clause to the statement, as well as a name for the new table as shown with the syntax below:
SELECT
[BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
INTO dbo.Test
FROM [AdventureWorks2017].[Person].[Person];
After executing the script, a new table will be created in the database:
In the messages window, you can see how many rows were inserted into this new table. When doing a straight SELECT from a table, the new table will have the same column names (when not using column aliases) and data types as the original table:
Even the NameStyle column is the same, which uses a user defined data type. If we use expressions in the SELECT statement, the data type will be determined on-the-fly based on the expressions used. For example, let's concatenate all the names of the Person table.
SELECT
[BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,FullName = [FirstName] + ' ' + ISNULL([MiddleName],'') + ' ' + [LastName]
,[Suffix]
,[EmailPromotion]
INTO dbo.TestWithExpression
FROM [AdventureWorks2017].[Person].[Person];
In the resulting table, we can see the FullName column is of type NVARCHAR(152).
The length of 152 is calculated by taking the 3 source columns – each of length 50 – together with the two spaces used in the concatenation.
Filtering Data with T-SQL
You can limit the number of rows inserted in the new table. You can either use a TOP clause if it doesn't really matter which rows are inserted:
SELECT TOP(100)
[BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,FullName = [FirstName] + ' ' + ISNULL([MiddleName],'') + ' ' + [LastName]
,[Suffix]
,[EmailPromotion]
INTO dbo.TestTop
FROM [AdventureWorks2017].[Person].[Person];
Or you can use the WHERE clause if you want to have more control over which rows are inserted. In the following example, we're only selecting rows of persons with a first name starting with the letter B.
SELECT
[BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,FullName = [FirstName] + ' ' + ISNULL([MiddleName],'') + ' ' + [LastName]
,[Suffix]
,[EmailPromotion]
INTO dbo.TestWhere
FROM [AdventureWorks2017].[Person].[Person]
WHERE [FirstName] LIKE 'B%'; -- WHERE condition
Sometimes you need to create a new table to insert data into, but there are many columns and it would take some time to type it all out manually. If you have the SELECT statement that you're going to use later, you can use the INTO clause to quickly create your table. But what if the SELECT statement takes a very long time? By filtering out all of the data, you can still create the table without waiting! We can do this by adding a WHERE clause where the result is always false.
For example:
SELECT [BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,FullName = [FirstName] + ' ' + ISNULL([MiddleName],'') + ' ' + [LastName]
,[Suffix]
,[EmailPromotion]
INTO dbo.TestNoData
FROM [AdventureWorks2017].[Person].[Person]
WHERE 1 = 0;
Now you have a table with no data, and you can script the CREATE TABLE statement using SSMS:
You now have the CREATE TABLE statement for your new table and you can make some adjustments, like setting the nullability of the columns, changing data types, setting a filegroup, configuring foreign and primary keys and so on. With this little trick, you can save yourself some time when creating tables.
More Complex SQL
The INTO clause is used in a SELECT statement, and this statement can be as complex as you want it to be. You can use joins, GROUP BY, UNION and so on. In fact, every example from the tip SQL Server SELECT Examples can be used. Just add INTO [TableName] right before the FROM clause. From that tip, we can for example use a query with an INNER JOIN, a WHERE clause, a GROUP BY and an ORDER BY clause:
SELECT
p.[FirstName]
,COUNT(1) AS RowCnt
INTO dbo.TestComplex
FROM [Person].[Person] p
INNER JOIN [HumanResources].[Employee] e ON e.[BusinessEntityID] = p.[BusinessEntityID]
WHERE p.[FirstName] LIKE 'Rob%'
GROUP BY [p].[FirstName]
ORDER BY [RowCnt] ASC;
Other Use Cases
Creating a Table in Another Database
In the previous examples, the created table was always in the same database as the source tables from the SELECT query. However, it's perfectly possible to have the destination table in another database if you specify a "three-part-name". This is database.schema.tablename.
Taking our very first example:
SELECT
[BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
INTO Test.dbo.DataFromOtherDB
FROM [AdventureWorks2017].[Person].[Person];
There are a couple of prerequisites:
- The destination database has to exist already. You can create tables on the fly, but not databases. You also must have permission to create tables in the database.
- This doesn't work in Azure SQL DB, where cross-database queries are not permitted. If you do try it, you'll get the following error:
Creating Temp Tables
Each time we've created a new "persisted" table in a database. But with the INTO clause you can also create temporary tables. These are tables stored in the tempdb database, and they are deleted once the connection is over.
Using our first example again, we can create a local temp table:
SELECT
[BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
INTO #MyLocalTempTable
FROM [AdventureWorks2017].[Person].[Person];
We can indeed find a new temporary table in tempdb:
Once the connection drops, the table will be deleted. A local temp table only exists for the connection that created it. Because it might be possible for multiple connections to create temp tables with the same name, a suffix is added to the local temp table in tempdb.
The full name of our temp table is actually:
A global temp table (which has two # before its name) is a temporary tables that can be shared between multiple connections. Let's create such a global temp table with INTO:
SELECT
[BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
INTO ##MyGlobalTempTable
FROM [AdventureWorks2017].[Person].[Person];
It's added again to the tempdb, but this time there's no suffix:
If you still have the query window open where you created the global temp table, you can create a new query window in SSMS and query the global temp table:
As long as there is at least one connection open that is referencing the global temp table, it will continue to exist. Once every connection is closed, it will be dropped as well.
Using INTO and temp tables is useful when you want to dump data into a table, but you only have use for it for a limited time. For example, when you are debugging a query, or in a stored procedure when you want to write intermediate results to disk. Once the stored proc is over, the data can be discarded.
Even though you can insert into a new temp table, you cannot use SELECT … INTO to create a table variable.
Limitations
There are some drawbacks when you use SELECT … INTO to create a new table.
- You cannot create a partitioned table. Even if the source table used in the SELECT is partitioned, the new table will not be partitioned.
- You cannot specify indexes, constraints, computed columns or triggers. Just like with partitioning, those properties are not transferred from the source table. One exception is the IDENTITY constraint, but there are a couple of conditions. You can read about it here.
- If you specify an ORDER BY clause, the order of the inserted rows is still not guaranteed.
Next Steps
- A good introduction to the SELECT … INTO construct can be found in the tip Creating a table using the SQL SELECT INTO clause - Part 1 and Part 2. In this tip, Sergey introduces the construct with easy to follow examples and he also explains some more advanced concepts such as specifying a filegroup or IDENTITY constraint.
- SQL Server 2017 had some enhancements for the INTO clause. You can read all about it in the tip SELECT...INTO Enhancements in SQL Server 2017.
- You can learn more about temporary tables in the tip Local vs Global SQL Server Temporary Tables.
- Using SELECT … INTO can have some performance benefits as the operation is minimally logged. You can read more about performance in the following tips:
- If you want to use INSERT INTO … SELECT, you can learn more about it in the tip SQL INSERT INTO SELECT Examples.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-01-10
