Open In App

Table Variable in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

Table variable is a type of local variable that used to store data temporarily, similar to the temp table in SQL Server. Tempdb database is used to store table variables.

To declare a table variable, start the DECLARE statement. The name of table variable must start with at(@) sign. The TABLE keyword defines that used variable is a table variable. After the TABLE keyword, define column names and datatypes of the table variable in SQL Server.

Syntax :

DECLARE @TABLEVARIABLE TABLE
(column1 datatype,  
column2 datatype,  
columnN datatype
)

Example-1 :
DECLARE @WeekDays TABLE (Number INT, Day VARCHAR(40), Name VARCHAR(40))

INSERT INTO @WeekDays
VALUES

(1, 'Mon', 'Monday'),
(2, 'Tue', 'Tuesday'),
(3, 'Wed', 'Wednesday'),
(4, 'Thu', 'Thursday'),
(5, 'Fri', 'Friday'),
(6, 'Sat', 'Saturday'),
(7, 'Sun', 'Sunday')

SELECT * FROM @WeekDays;

Number Day Name
1 Mon Monday
2 Tue Tuesday
3 Wed Wednesday
4 Thu Thursday
5 Fri Friday
6 Sat Saturday
7 Sun Sunday

Update and delete statement usage for table variable in SQL Server

Here we will update and delete the data in the table variables.

Example-2 :

DELETE @WeekDays WHERE Number=7;

UPDATE @WeekDays SET Name='Saturday is a holiday' WHERE Number=6 ;
SELECT * FROM @WeekDays;
Number Day Name
1 Mon Monday
2 Tue Tuesday
3 Wed Wednesday
4 Thu Thursday
5 Fri Friday
6 Sat Saturday is a holiday

Last Updated : 24 Sep, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads