What is a Table Variable? |Declare table variable

What is a Table Variable?

In simple language, a table variable in SQL is like a temporary table that you can use to store data within a specific query or batch of queries. It helps you to perform multiple operations on a system without creating a permanent table.

You can use the table variable to store data while you're working on something, just like writing down information on a sticky note. It's handy because you don't need to create a permanent table in a database.

How to Declare Table Variable?

You use the DECLARE statement to declare a table variable, and give it a name, just like creating any other variable. You also define the structure of the table by specifying the column names and their data types.

Here's an example:

Syntax of DECLARE TABLE

        
          DECLARE @tableVariable TABLE (
            Column1 DataType1,
            Column2 DataType2,
            ...
          );

In this syntax, @tableVariable is the name you give to your table variable. You can choose any valid variable name, starting with "@".
Column1, Column2, and so on are the names of the columns in your table variable.DataType1, DataType2, and so on represent the data types for each column.
You would replace them with appropriate data types like INT, VARCHAR, DATE, etc...

How to insert data into table variable?

Once the table variable is declared, you can insert data into it using the INSERT INTO statement, just like inserting data into a regular table.

Syntax of INSERT INTO

      
        INSERT INTO @tableVariable (Column1, Column2, ...)
        VALUES
            (Value1, Value2, ...),
            (Value1, Value2, ...),
            ...;

Once you have inserted data into the table variable , you can use it in your SQL queries just like a regular table.

For example:

Syntax of SELECT statement

      
        SELECT Column1, Column2
        FROM @tableVariable
        WHERE ...;

In this article, we have taken a example to understand how to create table variable or perform declare statement using SQL.

Syntax of DECLARE TABLE

      
        DECLARE @myTableVariable TABLE (
            Column1 INT,
            Column2 VARCHAR(50),
            Column3 DATE
        );

In this example, we declared a table variable named @myTableVariable with three columns: Column1 of type INT, Column2 of type VARCHAR(50), and Column3 of type DATE. You can define the columns and their data types according to your specific requirements.

Once the table variable is declared, you can perform various operations on it, such as inserting data, updating records, or querying the data using standard SQL statements.

Here's an example of inserting data into a table variable:

Syntax of INSERT INTO

      
        INSERT INTO @myTableVariable (Column1, Column2, Column3)
        VALUES
            (1, 'Value 1', '2023-01-01'),
            (2, 'Value 2', '2023-01-02'),
            (3, 'Value 3', '2023-01-03');

In this example, we used the INSERT INTO statement to insert three rows of data into the @myTableVariable table variable.

You can also use the table variable in various SQL operations, such as joining it with other tables, filtering data based on certain conditions, or returning the result set from a function or stored procedure.

Table variables are useful when you want to perform complex operations on a subset of data or pass data between different parts of your SQL script. They have a limited scope and exist only within the query where they are declared.

Exercise on a Table Variable.

Here's an exercise example using a table variable:

Let's say we want to calculate the total salary of employees in a specific department. We have the following employee data:

EmployeeID	FirstName	LastName	Department	Salary
1	Ramesh	Gupta	Sales	50000
2	Meena	Panchal	Sales	60000
3	Raju	Gujar	Marketing	70000
4	Ranjana	Mane	Marketing	55000
5	Kavita	Rane	Finance	80000

Here's how you can use a table variable to calculate the total salary for a specific department

(e.g., Sales):

Explanation:

In this example:

We declare a table variable @EmployeeTable with the same structure as the employee data table.
We insert the employee data into the table variable using the INSERT INTO statement.
We declare a variable @TotalSalary to store the total salary.
We use the SUM function to calculate the sum of salaries for employees in the Sales department from the table variable.
We assign the calculated total salary to the @TotalSalary variable.
Finally, we select the value of @TotalSalary as TotalSalesSalary to display the total sales salary.

When you successfully execute this query i will show you following result:

Conclusion:

A table variable is like a temporary, simplified table that you create inside your computer System to hold data temporarily while you're working on something. It's useful when you want to perform an operations or work with specific data without creating a permanent table in a database.

Overall, table variables provide a handy way to work with temporary data in SQL without the need for creating permanent tables.

What is a Table Variable? |Declare table variable