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

My Personal Notes arrow_drop_up

Save