Stack Overflow
Stack Overflow

Questions tagged [table-variable]

A SQL Server local variable that can store rows.

From MSDN:

Is a special data type that can be used to store a result set for processing at a later time. table is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function.

A table variable differs from a "temporary table" in that it has

  • no statistics
  • limited index options
  • execution plans often assume single row
  • does not participate in user transactions
  • for the batch/proc scope only
170 questions
444
votes
8 answers

SELECT INTO a table variable in T-SQL

Got a complex SELECT query, from which I would like to insert all rows into a table variable, but T-SQL doesn't allow it. Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. …
Indrek
  • 6,516
  • 4
  • 29
  • 27
425
votes
13 answers

What's the difference between a temp table and table variable in SQL Server?

In SQL Server 2005, we can create temp tables one of two ways: declare @tmp table (Col1 int, Col2 int); or create table #tmp (Col1 int, Col2 int); What are the differences between these two? I have read conflicting opinions on whether @tmp still…
Eric Z Beard
  • 37,669
  • 27
  • 100
  • 145
332
votes
6 answers

When should I use a table variable vs temporary table in sql server?

I'm learning more details in table variable. It says that temp tables are always on disk, and table variables are in memory, that is to say, the performance of table variable is better than temp table because table variable uses less IO operations…
yman
  • 3,385
  • 3
  • 15
  • 9
234
votes
3 answers

Creating an index on a table variable

Can you create an index on a table variable in SQL Server 2000? i.e. DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) Can I create an index on Name?
GordyII
  • 7,067
  • 16
  • 51
  • 69
202
votes
9 answers

How to see the values of a table variable at debug time in T-SQL?

Can we see the values (rows and cells) in a table valued variable in SQL Server Management Studio (SSMS) during debug time? If yes, how?
Faiz
  • 5,331
  • 10
  • 45
  • 57
159
votes
8 answers

How do I drop table variables in SQL-Server? Should I even do this?

I have a table variable in a script (not a stored procedure). Two questions: How do I drop the table variable? Drop Table @varName gives an "Incorrect snytax" error. Should I always do this? I hear it's a good practice. Is it ever really…
jtpereyda
  • 6,987
  • 10
  • 51
  • 80
78
votes
11 answers

Can I loop through a table variable in T-SQL?

Is there anyway to loop through a table variable in T-SQL? DECLARE @table1 TABLE ( col1 int ) INSERT into @table1 SELECT col1 FROM table2 I use cursors as well, but cursors seem less flexible than table variables. DECLARE cursor1 CURSOR FOR…
Kuyenda
  • 4,529
  • 11
  • 46
  • 64
76
votes
8 answers

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 procedure. I am using Sql Server 2008. This…
Ashar Syed
  • 1,236
  • 6
  • 16
  • 28
47
votes
1 answer

Compound primary key in Table type variable

SQL Server 2008: DECLARE @MyTable TABLE( PersonID INT NOT NULL, Person2ID INT NOT NULL, Description NVARCHAR(100), CONSTRAINT PK PRIMARY KEY CLUSTERED (PersonID, Person2ID) ); Gives: Msg 156, Level 15, State 1, Line 5 Incorrect syntax…
pkario
  • 2,180
  • 6
  • 26
  • 30
44
votes
8 answers

Table variable error: Must declare the scalar variable "@temp"

I am trying to achieve: declare @TEMP table (ID int, Name varchar(max)) insert into @temp SELECT ID, Name FROM Table SELECT * FROM @TEMP WHERE @TEMP.ID = 1 <--- ERROR AT @TEMP.ID But I'm getting the following error: Must declare the…
objectWithoutClass
  • 1,631
  • 3
  • 14
  • 15
41
votes
3 answers

TSQL Define Temp Table (or table variable) Without Defining Schema?

Is there a way to define a temp table without defining it's schema up front?
Jeff
  • 8,020
  • 34
  • 99
  • 157
37
votes
5 answers

How can I reseed an identity column in a T-SQL table variable?

I have a T-SQL table variable (not a table) which has an auto incrementing identity column. I want to clear all data from this variable and reset the identity column value to 1. How can this be done?
Nimesh Madhavan
  • 6,290
  • 6
  • 44
  • 55
37
votes
2 answers

PostgreSQL table variable

Is there anything like table variables in T-SQL? In Sql Server it looks like this: DECLARE @ProductTotals TABLE ( ProductID int, Revenue money ) Then in procedure I can: INSERT INTO @ProductTotals (ProductID, Revenue) SELECT ProductID,…
Yavanosta
  • 1,480
  • 3
  • 18
  • 27
33
votes
1 answer

How to fix "Must declare the scalar variable" error when referencing table variable?

I can't figure out why (or maybe you just can't do this) I get the out of scope error Must declare the scalar variable "@CompanyGroupSites_Master. So is it that I cannot access my Table variable this way inside my Cursor, or I must have missed…
PositiveGuy
  • 46,620
  • 110
  • 305
  • 471
31
votes
1 answer

Creating table variable in SQL server 2008 R2

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…
Pankaj
  • 501
  • 1
  • 5
  • 16
1
2 3
…
11 12