In this article we will explain how to use Table variables in SQL Server OR how to create Table variables in SQL Server with Example.


Table variables are objects similar to temporary tables and were introduced in SQL Server 2000. A table variable is declared using the table data type. A statement declaring a table variable initializes the variable as an empty table with a specified structure. As a table definition, such a statement includes definitions of columns with their data type, size, precision, and optional constraints (primary key, identity, unique, and check constraints). All elements have to be defined during the declaration. It is not possible to alter or add them later. It acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.

 The following batch declares a table variable, inserts rows, and returns them to the user:

 --Create Table variable @ProductsSales
 DECLARE @ProductsSales TABLE
 ProductID INT,
 Qty INT,
 SaleType VARCHAR(100)
 --Insert data to Table variable @ProductsSales
 INSERT INTO @ProductsSales(ProductID,Qty,SaleType)
 INSERT INTO @ProductsSales(ProductID,Qty,SaleType)
  --Select data
 Select * from @ProductsSales
 --Next batch
 Select * from @ProductsSales --gives error in next batch

I hope this article will help to you.

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

You may also like