Table Variable in SQL Server
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
Share your thoughts in the comments
Please Login to comment...