Table Variables
Learn how to declare, insert, query, update and delete data using Table Variables in SQL Server.
What is a Table Variable?
A Table Variable is a variable that can temporarily store multiple rows of data in SQL Server.
Table variables are declared using the DECLARE statement and the TABLE keyword.
They are useful when we need to store a small amount of temporary data during a SQL batch, stored procedure or function.
Syntax of Table Variable
DECLARE @TableName TABLE
(
Column1 DataType,
Column2 DataType,
Column3 DataType
);
Creating a Table Variable
The following example creates a table variable named @Employees.
DECLARE @Employees TABLE
(
EmployeeId INT,
EmployeeName VARCHAR(100),
Department VARCHAR(100),
Salary DECIMAL(10,2)
);
Insert Data into Table Variable
INSERT INTO @Employees
(
EmployeeId,
EmployeeName,
Department,
Salary
)
VALUES
(1, 'Amit', 'IT', 70000),
(2, 'Rahul', 'HR', 60000),
(3, 'Priya', 'IT', 80000),
(4, 'Neha', 'Finance', 65000);
Select Data from Table Variable
SELECT *
FROM @Employees;
Output
| EmployeeId | EmployeeName | Department | Salary |
|---|---|---|---|
| 1 | Amit | IT | 70000 |
| 2 | Rahul | HR | 60000 |
| 3 | Priya | IT | 80000 |
| 4 | Neha | Finance | 65000 |
Filtering Table Variable Data
We can use WHERE conditions while querying a table variable.
SELECT
EmployeeName,
Department,
Salary
FROM @Employees
WHERE Salary > 65000;
Update Data
UPDATE @Employees
SET Salary = 75000
WHERE EmployeeId = 1;
Table variables support UPDATE operations just like regular tables.
Delete Data
DELETE FROM @Employees
WHERE EmployeeId = 4;
Table Variable with Primary Key
A table variable can be declared with constraints such as a PRIMARY KEY.
DECLARE @Employees TABLE
(
EmployeeId INT PRIMARY KEY,
EmployeeName VARCHAR(100),
Department VARCHAR(100),
Salary DECIMAL(10,2)
);
Table Variable with IDENTITY
An IDENTITY column can be used when we want SQL Server to automatically generate numeric values.
DECLARE @Employees TABLE
(
EmployeeId INT IDENTITY(1,1),
EmployeeName VARCHAR(100),
Department VARCHAR(100),
Salary DECIMAL(10,2)
);
Insert Data using SELECT
Data from an existing table can be inserted into a table variable using SELECT.
DECLARE @ITEmployees TABLE
(
EmployeeId INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);
INSERT INTO @ITEmployees
SELECT
EmployeeId,
EmployeeName,
Salary
FROM Employees
WHERE Department = 'IT';
SELECT *
FROM @ITEmployees;
Table Variable with JOIN
Table variables can also participate in JOIN operations.
DECLARE @EmployeeDetails TABLE
(
EmployeeId INT,
EmployeeName VARCHAR(100),
DepartmentId INT
);
INSERT INTO @EmployeeDetails
SELECT
EmployeeId,
EmployeeName,
DepartmentId
FROM Employees;
SELECT
e.EmployeeName,
d.DepartmentName
FROM @EmployeeDetails e
INNER JOIN Departments d
ON e.DepartmentId = d.DepartmentId;
Table Variable in Stored Procedure
Table variables can be declared and used inside stored procedures.
CREATE PROCEDURE GetITEmployees
AS
BEGIN
DECLARE @Employees TABLE
(
EmployeeId INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);
INSERT INTO @Employees
SELECT
EmployeeId,
EmployeeName,
Salary
FROM Employees
WHERE Department = 'IT';
SELECT *
FROM @Employees;
END;
Temporary Table vs Table Variable
| Feature | Temporary Table | Table Variable |
|---|---|---|
| Declaration | CREATE TABLE #Table | DECLARE @Table TABLE |
| Prefix | # | @ |
| Scope | Session / Procedure scope | Batch / Procedure scope |
| Indexes | Supports indexes | Supports indexes through constraints/index definitions as applicable |
| Best suited for | Larger temporary datasets | Smaller temporary datasets |
Advantages of Table Variables
- Easy to declare and use.
- Useful for small temporary datasets.
- Can be used inside stored procedures and functions.
- Supports INSERT, UPDATE, DELETE and SELECT operations.
- Useful for storing intermediate results.
Important Points
- Table variables are declared using DECLARE @TableName TABLE.
- They are useful for temporary data processing.
- They support normal DML operations.
- They can contain constraints such as PRIMARY KEY.
- They are commonly used for smaller temporary datasets.
Summary
Table Variables provide a convenient way to store temporary data inside SQL Server batches, procedures and functions.
They are declared using the DECLARE statement and TABLE keyword and support common SQL operations such as SELECT, INSERT, UPDATE and DELETE.