SQL SELECT INTO Examples

By:   |   Updated: 2022-01-10   |   Comments (3)   |   Related: More > TSQL


   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)

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:

create new table with INTO

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:

data types in new 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).

data type as result of expression

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];
INTO with TOP clause

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
INTO with WHERE clause

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;
create table with no data

Now you have a table with no data, and you can script the CREATE TABLE statement using SSMS:

script CREATE TABLE in 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];
data dumped into another DB

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:
reference to database and/or server name is not supported in this version of SQL Server

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:

local temp table created

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:

[dbo].[#MyLocalTempTable___________________________________________________________________________________________________00000000003A]

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:

global temp table in tempdb

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:

query global temp table from another connection

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



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips


Article Last Updated: 2022-01-10

Comments For This Article




Tuesday, January 11, 2022 - 2:10:48 PM - Koen Verbeeck Back To Top (89649)
Hi Peter,
as I mentioned in the limitations section, indexes and constraints are not transferred from the source table. If you want them present, you either use a CREATE TABLE statement followed by an INSERT INTO statement, or you use the SELECT INTO and create every index/constraint with a CREATE INDEX/CONSTRAINT statement. I know, it's more work :)

Koen

Monday, January 10, 2022 - 7:53:44 PM - Peter Back To Top (89644)
G' Day KOen,
Thank you for your informative article on SELECT INTO.
When creating a new target table, I noticed that none of the keys and indexes were created in the new table.
What is the best way to do this.
KInd regards,
Peter H
Melbourne

Monday, January 10, 2022 - 4:39:50 PM - Joe F Celko Back To Top (89642)
The original SELECT INTO in ANSI/ISO standard SQL is known as the "Singleton SELECT" and had nothing to do with copying data from one table to another. Its function was to return zero or exactly one row from a query. It existed because early SQL products were still based on filesystems. This would open a file and buffer the first record (Rows and records were confused back then) in many of the early products. This statement disappeared from common usage as we became more set-oriented.


download














get free sql tips
agree to terms