SELECT INTO table variable

[Glasgow]

I have only just become aware of the option to use a table variable to substitute for a temporary table and I'd like to start taking this approach. Is it possible to direct the results of a SELECT INTO to a table variable without having declared any columns for that table - e.g. something like:

DECLARE @MyTable as Table ()
SELECT * INTO MyTable FROM Client

This doesn't work but hopefully you can see where I'm heading.

 

[TysonLPrice]

I don't think you can:

"Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement."

http://support.microsoft.com/default.aspx/kb/305977

 

[Glasgow]

Thanks for the reply. Yes you're right. When I first read that in BOL, I didn't (or perhaps just chose not to) see the word not. Back to temporary table then!

 

[SQLSister]

why go back to a temp table? Create a table variable with the correct fields and datatypes. Then use an insert statement to populate it.

"NOTHING is more important in a database than integrity." ESquared

 

[Crowley16]

table variables have MUCH better performance than temp tables, so even if you do have to do a little extra coding, it is worth while using them.

there is a caveat however, because table variables live completely in memory, if you have very little ram or have a big table variable, it could cause problems as processing the table variable could use up ALL your memory.

--------------------
Procrastinate Now!

 

[ca8msm]

table variables have MUCH better performance than temp tables,

Not necessarily. You should test both options based on the data set you are working with as temp tables can outperform table variables in some cases (and vice versa in others).


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Crowley16]

temp tables write to the temp db and create logs in the tempdb so has io to disk, whereas table variables live entirely in ram...

hdd is about 1000 times slower than ram.

As I have said before, the only exception is if you run out of ram, and that depends on the size of the table variable and the amount of ram sql server has access to.

--------------------
Procrastinate Now!

 

[ca8msm]

temp tables write to the temp db

So do table variables...


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ca8msm]

Oh, and here's a test on some sample tests to back up the theory that table variables are not always faster. As I've said before, you can't just say that they will always be faster as this is not correct. In some cases they will, in others they won't so you should really test with the data set you are working with.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Crowley16]

hmm, interesting reading that...

I stand corrected.

--------------------
Procrastinate Now!

 

[Glasgow]

why go back to a temp table?

Only because I don't want to maintain the same schema in 2 places. Dare I say that efficiency is not crucial in this instance (should I prepare for a hail of bullets?) - the script is just used as part of a very occasional database initialisation process.

Thanks for all the input.