T-SQL Tutorial

SQL Declare variable table


Table variables are a type of temporary data storage in SQL Server. They are similar to regular tables, but they exist only within the scope of a single batch or stored procedure and are not visible to other procedures or sessions. Table variables are created using the DECLARE statement and are prefixed with the @ symbol.

Table variables are useful when you need to store a small set of data that is used only within the scope of a single batch or stored procedure. They can also be used as a replacement for temporary tables in certain situations, such as when you need to pass a table as a parameter to a stored procedure.

One major advantage of table variables over temporary tables is that they have less locking and logging overhead, making them more efficient for small sets of data. Additionally, table variables are automatically cleaned up at the end of the batch or stored procedure, so you don't have to worry about manually dropping them.


Declare variable table

To declare a table variable, use the DECLARE keyword, then type the @variable_name and variable type table with columns.
To insert values into a table variable, uses the INSERT statement based on a SELECT statement.


Example

USE model;
GO
DECLARE @TableVariable table(course_name varchar(200), course_price int);
INSERT INTO @TableVariable (course_name, course_price)
SELECT name, price FROM Training_Course WHERE id in (1,2);
SELECT course_name, course_price FROM @TableVariable;
GO


Result

course_namecourse_price
SQL200
T-SQL700