SQL SERVER – SELECT INTO a Table Variable in T-SQL

Recently one of my oldest clients of Comprehensive Database Performance Health Check asked me a very interesting question about a table variable and SELECT INTO. The question was it is possible to do SELECT INTO a Table Variable in T-SQL? The answer is it is not possible at all. Let us understand what we can do in a similar situation.

SELECT INTO a Table Variable

The DBA at the client-side was trying to do the following:

SELECT CityName
INTO @Table1
FROM [Application].[Cities]

However, anytime when they were running this query the error which they were getting was as follows:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘@Table1’.

The reason is very simple, you can’t just insert the data into the table variable on the fly. There are two different solution/workaround to this problem.

Solution 1: Define Variable Table

This is a very simple solution, first, define a table variable and right after that insert the data into it.

DECLARE @Table1 TABLE (CityName VARCHAR(100));
INSERT INTO @Table1 (CityName)
SELECT CityName
FROM [Application].[Cities]

When you run the above statement it will work just fine.

Solution 2: Use Temp Table

However, it is quite possible that there are moments when we can’t define the table early on the hand. During that time we should use the temp table as the solution. Here is how you can do it.

SELECT CityName
INTO #Table1
FROM [Application].[Cities]

In the above syntax, the convenience is that we do not have to define the table definition early on the hand. The temporary table is created when the select statement is created. However, if the table already existed in the system, you may not be able to create it again and it will give you an error.

Well, that it. Today’s blog post is very simple. Here are a few additional blog posts related to the concept discussed in this blog post.

• SQL SERVER – How to Create Table Variable and Temporary Table?
• SQL SERVER – Table Variable or Temp Table – Performance Comparison – INSERT
• Where is Table Variable Created? – Interview Question of the Week #243
• SQL SERVER – Difference Temp Table and Table Variable – Effect of Transaction
• SQL SERVER – Table Variables or Temp Tables – Performance Comparison – SELECT

Reference: Pinal Dave (https://blog.sqlauthority.com)