T-SQL table variable Creating a table PHYSICAL!

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. WHAT FOR?? I thought it was only in my memory ?!

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

I need some serious help. I'm losing my mind!

Thanks in advance.

Answer 1

What do you experience in design :

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

Alternatives should be either:

• Do not define a WTF table and rely on behavior to automatically create it.

• Use existing code, but change SELECT INTO to INSERT:

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

Remember that when using TOP you must define an ORDER BY to ensure that data is returned sequentially.

Answer 2

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

Selecting INTO creates a physical table named "@WTF" as intended.

Answer 3

The secondary answer is that the reason it only works with brackets [] is the @ sign.

 select * from @WTF 

selects your empty table variable where

 select * from [@WTF] 

choosing a new physical table, it is selected into the created one, filled with data. Brackets are used to allow characters that are not normally allowed in the table or column name, so using them here means that you are looking for a table named @WTF instead of the WTF variable.

Answer 4

All table variables are “physical” tables.

Your belief that they are “only memory” is a myth. They are located in tempdb and are displayed in metadata views with names generated by the system, such as #4BAC3F29 . The structure of the table variable is identical to the #temp table.

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