SQL SERVER with Free ASP.NET Hosting :: Temporary Table With Master and Transaction Tables

Hi guys today I will 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.

    Create table MasterTable (  
    Id int identity (1, 1),  
    Name varchar(50)  
    ) 

    Create table TransactionTable (  
    t_id int identity (1, 1),  
    fk_Id int,  
    (Foreign key)  
    )

Create table MasterTable (

Id int identity (1, 1),

Name varchar(50)

)

Create table TransactionTable (

t_id int identity (1, 1),

fk_Id int,

(Foreign key)

)

Then we created a temporary table on the master table.

Create table #tempTest  
(  
Cid int identity (1, 1),  
Id int,  
Name varchar(50)  
) 

Insert into #tempTest(Id,Name)  
select  
Id,  
Name  
from  MasterTable

Create table #tempTest

(

Cid int identity (1, 1),

Id int,

Name varchar(50)

)

Insert into #tempTest(Id,Name)

select

Id,

Name

from MasterTable

Now you can see the original data in a temporary table using a select statement.

    select * from #tempTest

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.

    Declare @var int = 1 While @var <= ( Select COUNT(*) from #tempTest)  
        Begin Declare @tempid int   
      Select   
        @tempid = Id   
      from   
        #tempTest where Cid=@var   
      select   
        @tempid Insert into MasterTable(Name)   
      Select name from  MasterTable   
      where Id = @tempid --select * from #tempTest   
      set   
        @var = @var + 1   
      Drop table #tempTest   
        END

Declare @var int = 1 While @var <= ( Select COUNT(*) from #tempTest)

Begin Declare @tempid int

Select

@tempid = Id

from

#tempTest where Cid=@var

select

@tempid Insert into MasterTable(Name)

Select name from MasterTable

where Id = @tempid --select * from #tempTest

set

@var = @var + 1

Drop table #tempTest

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.

Declare @identity int  
set @identity =@@IDENTITY  
--select @identity  
Insert into TransactionTable(fk_Id)  
Select @identity from Acct_Test2 where fk_id=@tempid

Declare @identity int

set @identity =@@IDENTITY

--select @identity

Insert into TransactionTable(fk_Id)

Select @identity from Acct_Test2 where fk_id=@tempid

The following is the Stored Procedure to do that.

Create Procedure Procedurename  
As  
Create table #tempTest   
(  
Cid int identity (1,1),   
Id int,   
Name varchar(50)  
)   
Insert into #tempTest(Id,Name)  
select Id, Name from MasterTable  
  
--select COUNT(*) from #tempTest  
select * from #tempTest  
  
Declare @var int=1  
While @var <= (Select COUNT(*) from #tempTest)  
Begin  
Declare @tempid int  
Select @tempid = Id from #tempTest where Cid=@var   
select @tempid  
  
Insert into MasterTable(Name)   
Select name from MasterTable where Id=@tempid   
--select * from #tempTest   
  
Declare @identity int  
set @identity =@@IDENTITY   
--select @identity -76  
  
Insert into TransactionTable(fk_Id)   
Select @identity from TransactionTable where fk_id=@tempid   
  
set @var=@var+1   
Drop table #tempTest   
END  
--SELECT * FROM [RTU_Test].[dbo].[MasterTable]  
--Select fk_id from TransactionTable

Create Procedure Procedurename

Create table #tempTest

(

Cid int identity (1,1),

Id int,

Name varchar(50)

)

Insert into #tempTest(Id,Name)

select Id, Name from MasterTable

--select COUNT(*) from #tempTest

select * from #tempTest

Declare @var int=1

While @var <= (Select COUNT(*) from #tempTest)

Begin

Declare @tempid int

Select @tempid = Id from #tempTest where Cid=@var

select @tempid

Insert into MasterTable(Name)

Select name from MasterTable where Id=@tempid

--select * from #tempTest

Declare @identity int

set @identity =@@IDENTITY

--select @identity -76

Insert into TransactionTable(fk_Id)

Select @identity from TransactionTable where fk_id=@tempid

set @var=@var+1

Drop table #tempTest

END

--SELECT * FROM [RTU_Test].[dbo].[MasterTable]

--Select fk_id from TransactionTable

BEST RECOMMENDED SQL SERVER HOSTING

ASPHostPortal.com

ASPHostPortal.com is Perfect, suitable SQL SERVER hosting plan. ASPHostPortal the leading provider of Windows hosting and affordable SQL SERVER Hosting. ASPHostPortal proudly working to help grow the backbone of the Internet, the millions of individuals, families, micro-businesses, small business, and fledgling online businesses. ASPHostPortal has ability to support the latest Microsoft and ASP.NET technology, such as: WebMatrix, WebDeploy, Visual Studio 2015, .NET 5/ASP.NET 4.5.2, ASP.NET MVC 6.0/5.2, Silverlight 6 and Visual Studio Lightswitch, ASPHostPortal guarantees the highest quality product, top security, and unshakeable reliability, carefully chose high-quality servers, networking, and infrastructure equipment to ensure the utmost reliability.

HostForLIFE.eu

HostForLIFE.eu guarantees 99.9% uptime for their professional SQL SERVER 2014 hosting and actually implements the guarantee in practice. HostForLIFE.eu is the service are excellent and the features of the web hosting plan are even greater than many hosting. HostForLIFE.eu offer IT professionals more advanced features and the latest technology. HostForLIFe Best , Cheap SQL SERVER 2014 Hosting, Relibility, Stability and Performance of servers remain and TOP priority. Even basic service plans are equipped with standard service level agreements for 99.99% uptime. Advanced options raise the bar to 99.99%. HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24×7 access to their server and site configuration tools.

DiscountService.com.au

DiscountService.com.au is The Best and Cheap SQL SERVER Hosting. DiscountService.com.au was established to cater to an under served market in the hosting industry web hosting for customers who want excellent service. DiscountService.com.au guarantees the highest quality product, top security, and unshakeable reliability, carefully chose high-quality servers, networking, and infrastructure equipment to ensure the utmost reliability. DiscountService.com.au has ability to support the latest Microsoft and ASP.NET technology, such as: WebMatrix, WebDeploy, Visual Studio 2015, .NET 5/ASP.NET 4.5.2, ASP.NET MVC 6.0/5.2, Silverlight 6 and Visual Studio Lightswitch. DiscountService.com.au is devoted to offering the best Windows hosting solution for you

SQL SERVER with Free ASP.NET Hosting :: Temporary Table With Master and Transaction Tables

When to use a temporary table

BEST RECOMMENDED SQL SERVER HOSTING

ASPHostPortal.com

HostForLIFE.eu

DiscountService.com.au

About The Author

Ron

When to use a temporary table

BEST RECOMMENDED SQL SERVER HOSTING

ASPHostPortal.com

HostForLIFE.eu

DiscountService.com.au

Related Posts

About The Author

Ron