Creating table variable in SQL server 2008 R2

Question

what is table variable? And how to create a table variable (virtual in-memory table) with columns that match the existing Stored procedure resultset.

I executed the procedure and after executing it, the column names are known to me. But do i have to declare the same data type of the columns as it was in stored procedure?

EDIT: I tried this

DECLARE @Table TABLE( 
name varchar(30) NOT NULL, 
location varchar(30) NOT NULL 
); 

INSERT @Table 
SELECT name, location FROM 
Exec SPROC @param , @param

Just a word of warning: A table variable isn't guaranteed to be in memory. That's a myth. — JohnFx, Mar 13 '12 at 05:13
Can you include the code you have so far so we can answer your question? — JohnFx, Mar 13 '12 at 05:14
@JohnFx I havnn't executed code for the creation of table variable. I made a stored procedure and after executing it i got some column names, now i want those column names to be stored in table variable. And if it is not always in memory then how can i find it in my database, Sorry for troubling i am new on SQL. Please Help. — Pankaj, Mar 13 '12 at 05:20
To clarify: It might be on disk (depending on a number of factors) instead of memory, but that doesn't mean it will be a table that you can query directly. If you need that functionality go with a temp table instead of a table variable. — JohnFx, Mar 13 '12 at 15:09
possible duplicate of [SQL Server - SELECT FROM stored procedure](http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure) — JohnFx, Mar 13 '12 at 15:13
See the link in my possible duplicate comment above. I think that question (which already has lots of good answers) will get you the rest of the way. — JohnFx, Mar 13 '12 at 15:14
@JohnFx Both temp tables and table variables are actually stored in tempdb 100% of the time. There is no situation in which table variable is not in tempdb — Tom Stickel, Nov 05 '15 at 08:59

score 31 · Accepted Answer · edited Dec 11 '13 at 21:19

@tableName Table variables are alive for duration of the script running only i.e. they are only session level objects.

To test this, open two query editor windows under sql server management studio, and create table variables with same name but different structures. You will get an idea. The @tableName object is thus temporary and used for our internal processing of data, and it doesn't contribute to the actual database structure.

There is another type of table object which can be created for temporary use. They are #tableName objects declared like similar create statement for physical tables:

Create table #test (Id int, Name varchar(50))

This table object is created and stored in temp database. Unlike the first one, this object is more useful, can store large data and takes part in transactions etc. These tables are alive till the connection is open. You have to drop the created object by following script before re-creating it.

IF OBJECT_ID('tempdb..#test') IS NOT NULL
  DROP TABLE #test

Hope this makes sense !

Creating table variable in SQL server 2008 R2

1 Answers1