Understanding Temporary and Transaction Tables in SQL Server

When we develop an application we often need to provide temporary table functionality in our application.
 
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 is for a master table with an id as the primary key. The other table was a Transaction table that has the foreign key. The master table id should be transfered to the transaction table.

We initially create two tables named MasterTable and TransactionTable.
  1. Create table MasterTable  
  2. (  
  3.     Id int identity(1, 1),  
  4.     Name varchar(50)  
  5. )  
  6. Create table TransactionTable  
  7. (  
  8.     t_id int identity(1, 1),  
  9.     fk_Id int, (Foreign key)  
  10. )  
Then we create a temporary table on MasterTable.
  1. Create table#tempTest  
  2. (  
  3.     Cid int identity(1, 1),  
  4.     Id int,  
  5.     Name varchar(50)  
  6. )  
  7. Insert into#tempTest(Id, Name)  
  8. select Id, Name from MasterTable  
Now you can see that the original data in the temporary table uses 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 or intermediate results 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 create a loop on the master table to visit all the ids one by one.
  1. Declare @var int=1  
  2. While @var <= (Select COUNT(*) from #tempTest)  
  3. Begin  
  4. Declare @tempid int  
  5. Select @tempid = Id from #tempTest where Cid=@var   
  6. select @tempid  
  7.   
  8. Insert into MasterTable(Name)   
  9. Select name from MasterTable where Id=@tempid   
  10. --select * from #tempTest   
  11.   
  12. set @var=@var+1   
  13. Drop table #tempTest   
  14. END  
Now we need to check the 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.   
  5. Insert into TransactionTable(fk_Id)   
  6. 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  
  11.   
  12. --select COUNT(*) from #tempTest  
  13. select * from #tempTest  
  14.   
  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  
  21.   
  22. Insert into MasterTable(Name)   
  23. Select name from MasterTable where Id=@tempid   
  24. --select * from #tempTest   
  25.   
  26. Declare @identity int  
  27. set @identity =@@IDENTITY   
  28. --select @identity -76  
  29.   
  30. Insert into TransactionTable(fk_Id)   
  31. Select @identity from TransactionTable where fk_id=@tempid   
  32.   
  33. set @var=@var+1   
  34. Drop table #tempTest   
  35. END  
  36. --SELECT * FROM [RTU_Test].[dbo].[MasterTable]  
  37. --Select fk_id from TransactionTable