How To Create A Table Variable In SQL Server

Last week, our client required us to work with tabular data. After analyzing this requirement, we identified a few ways to do this. I will discuss creating a table variable in an SQL server using practical examples and scenarios in this article.

Table variables in SQL Server are like local variables that can hold tabular data. You can reference them using a stored procedure or batch operations.

Create a table variable in the SQL Server

Below is the syntax

Syntax

DECLARE @TableName TABLE (
    FirstColumn DataType,
    SecondColumn DataType,
    -- Add more columns as needed
);

Check out How To Check If Column Exists In SQL Server Database

Example

Let us discuss a real-time example of one critical Product from a USA-based MNC. We can declare table variables in the SQL server using the query below.

DECLARE @ProductInfo TABLE (
    ProductID INT,
    ProductName NVARCHAR(60),
    ProductType NVARCHAR(60),
    Reviews INT
);

Now, let us use the below query to insert data into it.

INSERT INTO @ProductInfo (ProductID, ProductName, ProductType, Reviews)
VALUES 
    (1, 'Dolo', 'Medicine', 5),
    (2, 'Mobile', 'Electronics',4);

Check out the screenshot below, where we executed the above queries successfully.

Now, we can call the table variable like the one below from any of the batch queries.

SELECT * FROM @ProductInfo WHERE ProductID = 2;

After executing the above query, I got the expected output, as shown below.

We can also declare the table variables with the primary key, like in the query below.

DECLARE @ProductInfo TABLE (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(60),
    ProductType NVARCHAR(60),
    Reviews INT
);

Check out How To Change Column Name In SQL Server

The screenshot below shows you can also declare table variables in an SQL server in a stored procedure.

Conclusion

Table variables in SQL Server are a great way to manage temporary data within a stored procedure or batch queries that help write more efficient and easy-to-maintain code. You can easily create a table variable in the SQL Server using the information mentioned in this article.

You may also like following the articles below.

• How To Enable CDC On A Table In SQL Server
• How To Create A Table Valued Function In SQL Server
• How To Check If Table Exists In SQL Server
• How To Find Column Name In Table SQL Server