CODEWITHSUNDEEP
sqlsql-servert-sql
Mike
Mike

Reputation: 223

T-SQL Table Variable Creating PHYSICAL Table!

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.

Upvotes: 4

Views: 2055

Answers (4)

Martin Smith
Martin Smith

Reputation: 453152

All table variables are "physical" tables.

Your belief that they are "memory only" is a myth. They reside in tempdb and are shown in the metadata views with system generated names such as #4BAC3F29. The structure of a table variable is identical to a #temp table.

You cannot use SELECT ... INTO with table variables but can do with #temp tables. Your code just creates a new user table called @WTF in your user database as indicated in the other answers.

Upvotes: 0

Ryan Elkins
Ryan Elkins

Reputation: 5797

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.

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 6

Randy Minder
Randy Minder

Reputation: 48402

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.

Upvotes: 4

Related Questions