Creating table variable in SQL server 2008 R2

sql-server-2008 table-variable
Pankaj picture Pankaj · Mar 13, 2012 · Viewed 145.9k times · Source

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

Answer

mangeshkt picture mangeshkt · Mar 15, 2012

@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 !

Related questions

How to use table variable in a dynamic sql statement?

In my stored procedure I declared two table variables on top of my procedure. Now I am trying to use that table variable within a dynamic sql statement but I get this error at the time of execution of that …

Read More
Inserting multiple rows into a SQL Server table using a table variable

I am currently using SQL Server 2008, and I am trying to create a statement using a table variable to insert multiple rows into the table. As it stands right now, I have to insert the information being added in 4 different …

Read More
Get definitive names for columns from table variable

I can declare a table variable as such: DECLARE @tv_source TABLE(c1 int, providerName varchar(50), providerSMS varchar(50)) If I then execute the following, I see the table name similar to: "#468862B0" select top 1 * from tempdb.sys.tables where type = …

Read More