T-SQL Table Variable Creating PHYSICAL Table!

Question

OMG! What am I doing wrong?

declare @WTF TABLE (
 OrderItemId int
)

SELECT TOP 20 OrderItemId as OrderItemId INTO [@WTF] FROM ac_OrderItems

SELECT * FROM [@WTF]

Problem A: This creates a PHYSICAL table called @WTF. WHY?? I thought this was in memory only?!

Problem B: The last line of code, if I do select * from @WTF... WITHOUT the [ ], it returns NOTHING. What is the significance of the [ ]?

I need serious help. I'm losing my MIND!

Thanks in advance.

Answer 1

Because Select INTO always creates a physical table. What you want to do is an Insert Into.

The Select INTO is creating a physical table named '@WTF', just as it's supposed to do.

Answer 2

What you experience is by design:

SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it.

The alternatives are to either:

1. Not define the WTF table, and rely on the behavior to create it automatically

2. Use the existing code, but change the SELECT INTO into an INSERT:

   INSERT INTO @WTF
     (orderitemid)
   SELECT TOP 20 
          oi.orderitemid
     FROM ac_ORDERITEMS oi
   

Mind that when using TOP, you should be defining an ORDER BY clause to ensure data is returned consistently.

Answer 3

The secondary answer is that the reason it seemed to only work with brackets [] is because of the @ sign.

select * from  @WTF

is selecting off of your empty table variable, where as

select * from  [@WTF]

is selecting off of the new physical table the select into created that was populated with data. The brackets are used to allow characters not normally allowed in a table or column name so their use here signifies you are looking for a table with the name @WTF instead of a variable named WTF.