Please disable your adblock and script blockers to view this page
 

Example to Declare and use Table Variables in SQL SERVER


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:
Get dates between two dates using sql table varaible

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." 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon