4

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.

Improve this question
4
  • 2
    How about removing the [ ] from the SELECT ... INTO ? Commented Mar 10, 2010 at 20:45
  • I had NO idea that SELECT INTO creates a PHYSICAL table! Thanks so much for everyones knowledge. You saved me from EATING A BULLET. Commented Mar 10, 2010 at 20:53
  • 1
    As an aside, don't assume that a table variable will ALWAYS be memory only. This article is rather outdated but is still mostly relevant and accurate: databases.aspfaq.com/database/… Commented Mar 10, 2010 at 22:16
  • 1
    The significance of the [] is that it makes it a literal. It will make a table called "@WTF" whereas you want to insert it into the variable table @WTF. The difference being that the SQL compiler thinks you really want the @ in the table name. Commented Dec 26, 2011 at 19:57

4 Answers 4

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

On point 1, of course you can't SELECT INTO a table variable; it must be declared first.
4

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.

Improve this answer

2 Comments

Ah, yeah, you got it. Removed my answer.
How would you re-write the select into query into a insert into?
2

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.