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

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.