What is a table variable and when would you need to use it? A table variable is a local variable. It is similar to temp tables. Table variables are created with a declaration statement, like other local variables. Like other local variables, a table variable name begins with an @ sign, but its declaration statement has a type of table. We can insert, update and delete the data contained in the table variables. Table variables are stored in the tempdb database, and not in memory.

The lifecycle of table variables starts at the declaration point and ends at the end of the batch. As a result, the table variable in SQL Server is automatically dropped at the end of the batch.

Suppose you have three columns that have the same type of data in them that you want to summarize in a table that only contains unique values. Perhaps you have a list of professions in three columns. The table contains people who have up to three different professions. You want to create a table of all the possible professions. This example is from the downloadable IMDB database.

declare @P as table (
  Professions NVARCHAR(50)  
);
declare @PDistinct as table (
  Professions NVARCHAR(50)  
);
insert into @P select distinct primaryProfession1 from Names
insert into @P select distinct primaryProfession2 from Names
insert into @P select distinct primaryProfession3 from Names

insert into @PDistinct select distinct Professions from @P WHere Professions Is not null

Select * from @PDistinct ORDER BY Professions

Once you have a table created you can insert the list of professions into a professions table. If your table is called professions then you would run the above script followed by the script below. Do not end the batch before running this script below. In other words, do not use the GO keyword to end the batch before running the line below.

insert into professions select * from @PDistinct ORDER BY Professions