Example to Declare and use Table Variables in SQL SERVER

Introduction: In this article I am going to explain what is table variables in sql server, how to create and use them with examples.

In  previous articles i explained Temporary tables in Sql server, their types and examples to use  and Difference between TemporaryTable and Table Variable in Sql

 and  How to Create and drop FOREIGN KEY CONSTRAINT on a table and Find second,third,fourth or nth highest salary of employee and Example to explain Transaction in Sql server using Asp.net  and What is cursor? Advantages and disadvantages,when to use and example to explain 

Description: The table data type is a special data type that can be used to store a result set or we can say intermediate results into a temporary structure for processing. As we know in single variable we can only store single item. But sometimes we need to work on multiple items. So whenever we want to store multiple records to use them further or perform calculations on them, we can use table variable or temporary tables.

To keep the article short and clear I am not going to discuss temporary tables in this article, I will explain that in my next article.

How to declare a table variable and insert data into it?

The syntax to declare a table variable is similar to the syntax of normal table. DECLARE keyword is used in place of CREATE keyword and table name (maximum of  128 characters) is prefixed with ‘@’ as all T-SQL variables do. 

There are two ways to insert data into table variable: 

1. Insert data into table variable as we do in normal table
2. Insert data into table variable directly from other existing table

First Way: Declare a table variable and insert data into it

--Declare a table variable

DECLARE @tbBooks TABLE

(

                 BookId    INT NOT NULL IDENTITY(1,1),

                 BookName  VARCHAR(100),

                 Author    VARCHAR(100),

                 Publisher VARCHAR(100),

                 Price     DECIMAL(10,2)

 )

--Insert data into table varaible

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn MVC','Lalit','Lalit Publications',1600.00)

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn ASP.NET','Neha','Neha Publications',1200.00)

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn SQL','Shaurya','Shaurya Publications',1150.00)

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn jquery','John','John Publications',1000.00)

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn Javascript','Scott','Scott Publications',900.00)

--Check inserted data

SELECT * FROM @tbBooks

Output will be as:

BookId	BookName	Author	Publisher	Price
1	Learn MVC	Lalit	Lalit Publications	1600.00
2	Learn ASP.NET	Neha	Neha Publications	1200.00
3	Learn SQL	Shaurya	Shaurya Publications	1150.00
4	Learn jquery	John	John Publications	1000.00
5	Learn Javascript	Scott	Scott Publications	900.00

Second Way: Declare a table variable and insert data into it directly from other existing table

Yes, we can also insert data directly into table variable from other existing table. To understand this let’s create a normal table and insert data into it.

--Create a table

CREATE TABLE tbBooks

(

                BookId                 INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

                BookName         VARCHAR(100),

                Author                 VARCHAR(100),

                Publisher            VARCHAR(150),

                Price                     DECIMAL(10,2)

)

--Insert data into table

INSERT INTO tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn MVC','Lalit','Lalit Publications',1600.00)

INSERT INTO tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn ASP.NET','Neha','Neha Publications',1200.00)

INSERT INTO tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn SQL','Shaurya','Shaurya Publications',1150.00)

INSERT INTO tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn jquery','John','John Publications',1000.00)

INSERT INTO tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn Javascript','Scott','Scott Publications',900.00)

Now let’s declare a table variable and insert data into it from tbBooks  table created above. Suppose we just want to get BookName and the Price from the tbBooks table and insert it into @tbBooks table variable, we need to execute the following script

DECLARE @tbBooks TABLE

(

                 BookName  VARCHAR(100),

                 Price     DECIMAL(10,2)

 )

INSERT INTO @tbBooks SELECT BookName,Price FROM tbBooks

SELECT * FROM @tbBooks

Output will be as:

BookName	Price
Learn MVC	1600.00
Learn ASP.NET	1200.00
Learn SQL	1150.00
Learn jquery	1000.00
Learn Javascript	900.00

Now suppose we want to insert data into @tbBooks table variable from tbBooks table based on condition i.e. only those records where price is greater than 1100 then execute the following:

DECLARE @tbBooks TABLE

(

                 BookName  VARCHAR(100),

                 Price     DECIMAL(10,2)

 )

 INSERT INTO @tbBooks SELECT BookName,Price FROM tbBooks WHERE Price > 1100

 SELECT * FROM @tbBooks

Output will be as:

BookName	Price
Learn MVC	1600.00
Learn ASP.NET	1200.00
Learn SQL	1150.00

Example to Use table variables to store and get all dates between two dates

DECLARE @StartDate AS DATE='2014-12-17';

DECLARE @EndDate AS DATE='2014-12-25';

DECLARE @DateList TABLE (iDate DATE,iDayName VARCHAR(10))

WHILE (@StartDate<=@EndDate)

BEGIN

                INSERT @DateList(iDate,iDayName) VALUES(@StartDate,DATENAME(DW,@StartDate))

                SET @StartDate=CAST(DATEADD(DAY,1,@StartDate) AS DATE)

END

SELECT iDate AS [Date], iDayName AS [DayName] FROM @DateList

Output will be as:

In above example we are using table variable to store and get all the dates and their day name between two dates.

Update & Delete records in table variable

We can perform update and delete operation in table variable as we do in normal table

Update in table variable:  Update operation on table variable is similar to normal table

--Declare a table variable

DECLARE @tbBooks TABLE

(

                 BookId    INT NOT NULL IDENTITY(1,1),

                 BookName  VARCHAR(100),

                 Author    VARCHAR(100),

                 Publisher VARCHAR(100),

                 Price     DECIMAL(10,2)

 )

--Insert data into table varaible

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn MVC','Lalit','Lalit Publications',1600.00)

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn ASP.NET','Neha','Neha Publications',1200.00)

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn SQL','Shaurya','Shaurya Publications',1150.00)

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn jquery','John','John Publications',1000.00)

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn Javascript','Scott','Scott Publications',900.00)

--Check inserted data

SELECT * FROM @tbBooks

--Update the record

UPDATE @tbBooks  SET Price=1250 WHERE BookId=4;

--Check data in table before update

SELECT * FROM @tbBooks

Output will be: 

BookId	BookName	Author	Publisher	Price
1	Learn MVC	Lalit	Lalit Publications	1600.00
2	Learn ASP.NET	Neha	Neha Publications	1200.00
3	Learn SQL	Shaurya	Shaurya Publications	1150.00
4	Learn jquery	John	John Publications	1250.00
5	Learn Javascript	Scott	Scott Publications	900.00

Note: The price of  Book with Id=4 is updated to 1250 from 1000 in the table variable

Delete from table variable: Delete operation in table variable is similar to normal table

--Declare a table variable

DECLARE @tbBooks TABLE

(

                 BookId    INT NOT NULL IDENTITY(1,1),

                 BookName  VARCHAR(100),

                 Author    VARCHAR(100),

                 Publisher VARCHAR(100),

                 Price     DECIMAL(10,2)

 )

--Insert data into table varaible

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn MVC','Lalit','Lalit Publications',1600.00)

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn ASP.NET','Neha','Neha Publications',1200.00)

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn SQL','Shaurya','Shaurya Publications',1150.00)

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn jquery','John','John Publications',1000.00)

INSERT INTO @tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn Javascript','Scott','Scott Publications',900.00)

--Check inserted data

SELECT * FROM @tbBooks

--Delete the record

DELETE FROM @tbBooks WHERE BookId=5;

--Check data in table after deleting the record

SELECT * FROM @tbBooks

BookId	BookName	Author	Publisher	Price
1	Learn MVC	Lalit	Lalit Publications	1600.00
2	Learn ASP.NET	Neha	Neha Publications	1200.00
3	Learn SQL	Shaurya	Shaurya Publications	1150.00
4	Learn jquery	John	John Publications	1250.00

Note: Record with the BookId=5 is deleted from the table variable

Important points about temporary variables

• Table variables are alternative of temporary tables
• Table variables can be used in user defined functions, stored procedures, and batches so its scope is in the stored procedure, user defined function or batch where it is declared like any local variable we create with a DECLARE statement
• Table variables are automatically cleaned up at the end of the user defined function, stored procedure, or batch in which they are defined.
• Table variables can’t be truncated like normal table or temporary tables.
• We can return a table variable from a user-defined function
• Table variables can’t be indexed using CREATE INDEX
• Table variables can’t be created using Select Into statement with which we can create normal or temporary tables.
• PRIMARY KEY, UNIQUE, DEFAULT values, NULL, CHECK can be added, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed. for example

           DECLARE @MyTable TABLE
           (
               ID                   INT IDENTITY(1,1) PRIMARY KEY,
               Age                 INT NOT NULL CHECK (Age>20),
               Name            VARCHAR(100)NOT NULL,
              [Address]      VARCHAR(200) NULL DEFAULT('Unknown')
           )

• Table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used.
• Does not support ALTER TABLE command
• The data in the table variable will not be rolled back when a transaction is rolled back
• Like temporary tables, table variables are also created in TempDB
• A table variable will generally use fewer resources than a temporary table

Now over to you:

"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates." 

• Tweet
• Share
• Share
• Share

Related Post