5

is it possible to declare a variable in tsql without defining columns explicitly ?

Something like 

declare @tab as select * from myTable
Tony
  • 12,405
  • 36
  • 126
  • 226
  • 2
    What you wanted to do is called "table variable". You have to define the columns. What @John suggests is called "temporary table". It's similar but different. And it can be declared implicitly (or even explicitly with a CREATE TABLE). If you google you'll find the little differences (the differences are in speed and in how they are "saved" in memory) – xanatos Oct 18 '11 at 13:33
  • @xanatos - Those aren't the differences. Both are saved in `tempdb`. Differences are statistics and recompiles. – Martin Smith Oct 18 '11 at 15:12
  • 1
    @MartinSmith http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/64891#64891 – xanatos Oct 18 '11 at 15:16
  • 1
    @MartinSmith I will say the most important for speed is `Table variables don't participate in transactions, logging or locking. This means they're faster as they don't require the overhead, but conversely you don't get those features.` – xanatos Oct 18 '11 at 15:16
  • @xanatos - Forgot about logging. Nowhere does that linked answer validate your claim about memory though. local `#temp` tables don't really have much/any locking overhead either as they are not visible to other transactions. – Martin Smith Oct 18 '11 at 15:17
  • @MartinSmith for "memory" I meant "any memory". So caching + disk. Table variables are surely more lightweight than temp tables. I have to tell the truth I thought that small table variables were only cached and not written on disk, but the sentence sounds still true. – xanatos Oct 18 '11 at 15:21
  • @MartinSmith And I rememebered correctly: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx `First, the table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb.` So it was as I thought, table variable aren't necessarily saved to disk (the sentence tells that they CAN be saved to disk, not that they ARE) – xanatos Oct 18 '11 at 15:23
  • @xanatos - If you try `DECLARE @T TABLE(X INT); INSERT INTO @T VALUES (1); SELECT sys.fn_PhysLocFormatter(%%physloc%%) FROM @T` you will see the File/Page/Slot in `tempdb` used to store the row. AFAIK whether or not the page gets flushed to disc depends on the memory grant for the query (exactly the same as for `#temp` tables) – Martin Smith Oct 18 '11 at 15:25
  • @xanatos - I never disputed that. But the same applies to `#temp` tables. They can be entirely memory resident too. What I was questioning was that there was any difference in behaviour between the two. – Martin Smith Oct 18 '11 at 15:27
  • @xanatos - [Looked into the logging issue and concluded that is pretty much a myth too](http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/8204184#8204184) – Martin Smith Nov 20 '11 at 19:55

2 Answers2

4

You can select into a temp table ... which looks like it will do what you're after.

select * 
into #myTempTable
from myTable
John MacIntyre
  • 12,910
  • 13
  • 67
  • 106
2

You must define columns when declaring a table varaible. If you want to do something like that you may need to revisit your design. If you want to create a proc that does anything to any table, that is a bad idea in SQL. Databases operate best when not designed generically and you should not be trying ito generalize queries.

And you should not be using select * for any production query anyway as it is a SQL Antipattern.

HLGEM
  • 94,695
  • 15
  • 113
  • 186