0

I'm using TSQL(SQL Server).

I started using Table variables for lightweight operations.

These are easy to use but I found populating these are a bit inconvenient or I'm missing something.

let's say I want to insert all rows from a table with 25 columns into a table variable.

The only I could make it work was to Define a Table variable with all 25 columns defined in it and then do the insert.

Is there a way that this can be done automatically? That is we don't define the columns inside temp table and they get generated dynamically onthe insert?

S Nash
  • 2,363
  • 3
  • 34
  • 64
  • No, unless you fancy generating *everything* dynamically -- including the table declaration statements, the inserts and whatever else processes them. That way lies madness, frankly. T-SQL hates dynamic result sets and it's not afraid to let you feel the pain. (You can `SELECT INTO` and `INSERT .. EXEC` to temp tables without having to resort to dynamic SQL, but that only solves half of the problem.) – Jeroen Mostert Aug 29 '19 at 12:55
  • There is some discussion on this [here](https://stackoverflow.com/questions/3838240/select-into-a-table-variable-in-t-sql) including alternate suggestions. – Peter Smith Aug 29 '19 at 12:57
  • 1
    Have you considering using temp tables? SELECT [YourColumns] INTO #TempTable FROM [YourTable] – Isaac Aug 29 '19 at 12:58
  • 1
    You should use temporary tables instead of table variables. They will achieve less functionality, but with less hassle and with some additional benefits (if you ever end up using them more intensively). – Radu Gheorghiu Aug 29 '19 at 13:01
  • @Jeroen Mostert, Yes I loved if everything was generated dynamically :). The tables I use Temp variable for are very small (between 100to 700 rows). – S Nash Aug 29 '19 at 13:02
  • @ Isaac. The main point is to try to use the speed of Table variables for very small tables. – S Nash Aug 29 '19 at 13:04
  • Thanks for comments. So I got my answer. This is not possible. – S Nash Aug 29 '19 at 13:05
  • 2
    Unless your table variables are explicitly declared as in-memory, they're not typically faster than temporary tables. In fact, they're often *slower* because of the lack of statistics, producing poorer execution plans. It's a common misconception that table variables are kept in memory by default -- they're not. – Jeroen Mostert Aug 29 '19 at 13:17

1 Answers1

0
If(OBJECT_ID('tempdb..#Temp') Is Not Null)
Begin
    Drop Table #Temp --If exists 
End

SELECT TableName, TableRowCount 
INTO #Temp
FROM tbl_TableName 

SELECT * FROM #Temp

Note:- You can use #Temp table instead of Table as Variable..

THE LIFE-TIME LEARNER
  • 1,476
  • 1
  • 8
  • 18
  • 1
    Probably because it doesn't answer the question. – Tab Alleman Aug 29 '19 at 13:16
  • 1
    Nor does it have any explanation. And it glosses over the fact that OP is using table variables while this suggestion uses temp tables - which are similar but still different things. – SMor Aug 29 '19 at 14:04