Create Table Variable in SQL Server

Updated on September 1, 2015     Kisan Patel

This tutorial will show you how to temporarily hold data into table variable in SQL Server?

  DECLARE @InactiveRecords AS TABLE
  (
   DetailId int,
   FullName varchar(50)
  )
  INSERT INTO @InactiveRecords
  SELECT EmployeeID, FirstName + ' ' + LastName FROM [NORTHWND].[dbo].[Employees]
  SELECT IR.FullName, ac.OrderDate, ac.ShipAddress FROM @InactiveRecords as IR,
  Orders ac WHERE IR.DetailId = ac.EmployeeID

In the above code snippet, we have declared a temporary table named “@InactiveRecords” with EmployeeID and FullName column. Next, we are filling this table with inactive data from[Employees]table and then using this temp table to join with Accounts and getting other details and listing it.

Important: It is mandatory to use alias with the temporary table while referencing in the SELECT query.

Remember that as this is a table variable, so the name must be prefixed with @ character. The scope of this variable exists only within session or stored procedure where it is declared.