Description

This article explains temporary tables with example in SQL Server.

When we develop an application, in many cases we need to use temporary tables in our applications.

So let's have a look at a practical example of how to create a Stored Procedure to insert values into multiple tables in SQL Server using a temporary table.

The example is developed in SQL Server 2012 using the SQL Server Management Studio.

The requirement was to use a master table containing an id as the primary key. The other table was a Transaction table with a foreign key.

The master table id should be transferred to the transaction table.

First we have created two tables named MasterTable and TransactionTable.

  1. Create table MasterTable (
  2. Id int identity (1, 1),
  3. Name varchar(50)
  4. )

  6. Create table TransactionTable (
  7. t_id int identity (1, 1),
  8. fk_Id int,
  9. (Foreign key)
  10. )
Then we created a temporary table on the master table.
  1. Create table #tempTest
  2. (
  3. Cid int identity (1, 1),
  4. Id int,
  5. Name varchar(50)
  6. )

  8. Insert into #tempTest(Id,Name)
  9. select
  10. Id,
  11. Name
  12. from MasterTable
Now you can see the original data in a temporary table using a select statement.
  1. select * from #tempTest
When to use a temporary table

If there is logic inside a Stored Procedure that involves manipulation of data that cannot be done within a single query, then in such cases, the output of one query / intermediate result can be stored in a temporary table that then participates in further manipulation via joins and so on to achieve the final result.

Now we need to transfer the primary key id in the transaction table. To do that we created a loop on the master table to visit all the ids one by one.
  1. Declare @var int = 1 While @var <= ( Select COUNT(*) from #tempTest)
  2. Begin Declare @tempid int
  3. Select
  4. @tempid = Id
  5. from
  6. #tempTest where Cid=@var
  7. select
  8. @tempid Insert into MasterTable(Name)
  9. Select name from MasterTable
  10. where Id = @tempid --select * from #tempTest
  11. set
  12. @var = @var + 1
  13. Drop table #tempTest
  14. END
Now we need to fetch the master table id and insert into the transaction table. In the master table we have applied an identity with a primary key. So we can fetch the last inserted value from the master table using @@IDENTITY.
  1. Declare @identity int
  2. set @identity =@@IDENTITY
  3. --select @identity
  4. Insert into TransactionTable(fk_Id)
  5. Select @identity from Acct_Test2 where fk_id=@tempid

The following is the Stored Procedure to do that.


  1. Create Procedure Procedurename
  2. As
  3. Create table #tempTest
  4. (
  5. Cid int identity (1,1),
  6. Id int,
  7. Name varchar(50)
  8. )
  9. Insert into #tempTest(Id,Name)
  10. select Id, Name from MasterTable
  12. --select COUNT(*) from #tempTest
  13. select * from #tempTest
  15. Declare @var int=1
  16. While @var <= (Select COUNT(*) from #tempTest)
  17. Begin
  18. Declare @tempid int
  19. Select @tempid = Id from #tempTest where Cid=@var
  20. select @tempid
  22. Insert into MasterTable(Name)
  23. Select name from MasterTable where Id=@tempid
  24. --select * from #tempTest
  26. Declare @identity int
  27. set @identity =@@IDENTITY
  28. --select @identity -76
  30. Insert into TransactionTable(fk_Id)
  31. Select @identity from TransactionTable where fk_id=@tempid
  33. set @var=@var+1
  34. Drop table #tempTest
  35. END
  36. --SELECT * FROM [RTU_Test].[dbo].[MasterTable]
  37. --Select fk_id from TransactionTable