---

Search posts under SQL Server:

---

Table variable in SQL Server

How to hold data temporarily into table variable in SQL Server?

While working with bigger and complex query, we might need to hold some data temporarily to use it or perform some query on. In these scenario, we can use Temporary table variable. The name of the temporary table variable starts with "@" as against temporary table whose name starts with #.

DECLARE @InactiveRecords AS TABLE
(
       PersonalDetailId int,
       FullName varchar(50)
)
INSERT INTO @InactiveRecords
SELECT PersonalDetailsId, FirstName + ' ' + LastName
       FROM PersonalDetails WHERE Active = 0

SELECT IR.FullName, ac.Salary, ac.PPFDeduction, IR.PersonalDetailId
FROM @InactiveRecords as IR, Accounts ac
WHERE IR.PersonalDetailId = ac.PersonalDetailsId

In the above code snippet, we have declared a temporary table variable named “@InactiveRecords” with PersonalDetailsId and FullName column. Next, we are filling this table with inactive data from PersonalDetails 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 variable 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.