SQL SERVER – How to Create Table Variable and Temporary Table?

This is a very simple question and indeed many of us know about this one. However, if you are absolutely beginner, you will for sure need help with this one. Let us understand how we can create a table variable and temporary table in this simple tutorial.

SQL SERVER - How to Create Table Variable and Temporary Table? tablevartemptable-1-800x288

Temporary Table

Let us first learn how to create a temporary table.

CREATE TABLE #TempTable (ID INT IDENTITY(1,1))
GO

Now you can query the table just like a regular table by writing select statement.

SELECT * FROM #TempTable

As long as the session is active you can query the same table multiple times.

The table will be automatically dropped when you close the connection. If you want to explicitly drop the table you can execute the following command.

DROP TABLE #TempTable
GO

Just remember, any table which is created with # in the beginning is a temporary table and it is created in the temp database.

Table Variable

Table variables are also temporary objects and they are created as with DECLARE keywords.

Once you have successfully created a table variable, you can query the same table variable via following select statement.

DECLARE @TableVariable TABLE (ID INT)
SELECT ID FROM @TableVariable ;

You do not have to drop the table variable because as soon as the statement execution is completed, the table variable does not exist. In this example also you have to declare and select both of the statement together otherwise you will face an error where it says that table variable does not exist.

Additionally, please do not use keyword GO between DECLARE and SELECT statement when you are using table variable as it will reset the declaration of the table variable.

Is Table Variable Memory Only Object?

Here is the article where I have explained previously that table variable are created in a temporary database just like temporary table. Both of them exist on the disk. Here is the article you must read today – SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Configure Stored Procedure to Run at Server Startup – Simple Tutorial
Next Post
SQL SERVER – How to Find Stored Procedure Execution Count and Average Elapsed Time?

Related Posts

5 Comments. Leave new

  • the pic says vice versa . pls correct it.

    Reply
  • Good concise article Dave – havent used these much but can see plenty of places where it would certainly help.

    Reply
  • Dave Colbourn
    April 25, 2018 5:53 am

    Help! I have an auto increment key and varchar(max) as a dimension and they get loaded first. Then I load the fact and need to find up to 5 surrogate keys just generated into the dimension. I am thinking associative entity as temp table that holds business key and surrogate being generated but I am modeler not an ETL guy. How do we pull it off?

    Reply

Leave a Reply

Menu