SQL Server Table Variable Examples
Let’s look at some examples of the SQL Server Table Variables, and understand how to declare, update, and delete table variables.
Declare Table Variable in SQL Server Example
In this example, we will declare a Table variable and insert values in it.
Query:
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;
Output:
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 Commands for table variable in SQL Server
In this example, we will update and delete the data in the table variables.
Query:
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 |
SQL Server Table Variable
SQL Server Table variable is a local variable that stores data temporarily, similar to the temporary table in SQL Server.
Tempdb database is used to store table variables.