Introduction
In my previous article I described
Temporary Tables in SQL. So there is one alternative approach also available to do the same thing as a temporary table. The alternative to a temporary table is a "Table Variable". We can perform similar operations using Table Variables but there is a difference that we will see further in this article.
Table Variable
A Table Variable is a variable that can store the complete table of the data inside it. It is similar to a Table Variable but as I said a Table Variable is a variable. So how do we declare a variable in SQL? Using the @ symbol. The same is true for a Table Variable. so the syntax of the Table Variable is as follows:
- Declare @<Variable_Name> TABLE(
- Column_Name [Data_Type],
- Column_Name [Data_Type],
- Column_Name [Data_Type],
- ......
- )
For
Example
- Declare @TempTable TABLE(
- id int,
- Name varchar(20)
- )
Now you can perform insert, update, delete and select all operations with it such as in the following, I write it like this:
- Declare @TempTable TABLE(
- id int,
- Name varchar(20)
- )
-
- insert into @TempTable values(1,'Sourabh Somani')
- insert into @TempTable values(2,'Shaili Dashora')
- insert into @TempTable values(3,'Divya Sharma')
- insert into @TempTable values(4,'Swati Soni')
-
- Select * from @TempTable
![select]()
Difference between temporary tables and Table Variable
There are a difference between temporary tables and temporary variables, it is:
- A Table Variable is not available after execution of the complete query so you cannot run a single query but a temporary table is available after executing the query.
For example:
![temporary tables and temporary variables]()
- A Transaction (Commit and Rollback) operation is not possible in a Table Variable but in a temporary table we can perform transactiona (Commit and Rollback).
For example:
- Declare @TempTable TABLE(
- id int,
- Name varchar(20)
- )
- begin tran T
- insert into @TempTable values(1,'Sourabh Somani')
- insert into @TempTable values(2,'Shaili Dashora')
- insert into @TempTable values(3,'Divya Sharma')
- insert into @TempTable values(4,'Swati Soni')
- commit tran T
- Select * from @TempTable
![Transaction]()
or
- Declare @TempTable TABLE(
- id int,
- Name varchar(20)
- )
- begin tran T
- insert into @TempTable values(1,'Sourabh Somani')
- insert into @TempTable values(2,'Shaili Dashora')
- insert into @TempTable values(3,'Divya Sharma')
- insert into @TempTable values(4,'Swati Soni')
- rollback tran T
- Select * from @TempTable
![Transaction operation]()
Important Points about Table Variables
- The same as a temporary table.
- Single query cannot be executed.
- When we want to perform a few operations then use a Table Variable otherwise if it is a huge amount of data operation then use a temporary table.
- Commit and Rollback (Transaction) cannot be possible with Table Variables so if you want to perform a transaction operation then always go with temporary tables.