Some finding on INTO Clause with TableVariable, TempVariable and TempTable

I will start with conclusion, there after we will try implementing and checking the conclusion.

On Local Temp Variable

Note: You cannot use Temp Variable in select into clause for the assignment. Use Set or = operator.

On Temp Table

Note: You can use select into clause to insert data from select query to Temp Table. INSERT INTO also work with Temp Variable.

On Table Variable

Note: You cannot use select into clause to insert data from select query to Table Variable, instated use INSERT INTO for insertion.

Let take example to find the conclusion correct.

Temp Variable, here we are declaring Temp Variable and then trying to assign value using into clause in select statement.

declare @row_count int

select count(1) into @row_count  from sys.columns

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '@row_count'.

So we can see it throws error. Now let’s assign temp variable with ‘=’ operator.

declare @row_count int

select @row_count = count(1) from sys.columns

select @row_count as [RowCount]

RowCount

-----------

3619

So with ‘=’ operator we can assign temp variable in select statement.  Let’s now try assignment using set operator.

declare @row_count int

set   @row_count = (select count(1)  from sys.columns)

select @row_count  as [RowCount]

RowCount

-----------

3619

Conclusion: Assignment of the temporary variable in select statement can be done through set operator, ‘=’ operator but not through into clause.

Temp Table: Now let’s try with Temp table, here we are declaring Temp Table and then trying to assign value using into clause in select statement.

select count(1) as countRow into #tempRow  from sys.columns

select * from #tempRow

countRow

-----------

3619

Conclusion: Here we can see, we can assign temp table using into clause.  But set and ‘=’ can’t worked out with Temp table since both this is operator is used for variable assignment.

 Table Variable: Here first we will try to insert data through “INTO” clause in the Table Variable, there after we will try with other option

declare @tableVar table (col1 int);

select count(1) as countRow into @tableVar  from sys.columns   

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '@tableVar'.

With the output we can see, we cannot do insertion into table variable through “INTO” Clause. Lets try out with Insert statement.

declare @tableVar table (col1 int)

insert into @tableVar

select count(1) from sys.columns

select * from @tableVar

col1

-----------

3619

Insert statement worked out with table variable.

And since it is Table Variable we cannot use set or ‘=’ operator to insert data in Table variable.

Boldly implementation of code logic will get your opposite number duck on your feet.

Post Reference: Vikram Aristocratic Elfin Share