CREATE TEMP TABLES ON THE FLY IN STORED PROCEDURES

There are instances where we need to create temp tables in a stored procedure. For example lets say i have a table Person which looks somewhat like this


Now if i were to create a temp table #Person in a stored proc to store the contents of the above table temporarily for some reason, i will need to create the table with the same structure as the table whose contents i want to store before i can actually use it in my stored proc, that is somewhat like below:

CREATE TABLE #Person
(
    Name            varchar(50),
    Surname         varchar(50),   
    State           varchar(50)
)


An easier way will be to use the following code

IF OBJECT_ID('tempdb..#Person') IS NULL
select * into #Person from [dbo].[Person]
else
truncate table #Person
insert into #Person select * from [dbo].[Person]


The above code checks for the presence of the temp table first (from an earlier execution in the  same session let's say). If it doesn't find one, it automatically creates it with the same structure as the destin ation table and copies the data. It it is present, the temp table is truncated to clear existing values and the insert happens subsequently.

9 comments:

Morgan said...

They were professional and able to work web builders independently when needed

JaeGraig said...

Their team provided UI design and typography in accordance with the brand’s style guidelines.
brand identity agency

John Harry said...

The team worked efficiently,mobile app firms responding to requests for changes quickly and enthusiastically.

Charles Null said...

The new designs attracted more web traffic and received positive feedback for their freshness and creativity
creative brand company

Unknown said...

UI design companies
Hey to everyone, it’s my first visit of the blog site; this blog includes awesome and actually best info for the visitors.

Karen Rogers said...

The stuff in the blogs blows out my mind.
professional logo designers

Jamie Mack said...

web design firm
I was pinning away for such type of blogs, thanks for posting this for us.

Stephanie Clifton said...

I want more and more articles and blogs please post soon such informative information.
UI UX design company

Harry Jack said...

Whatever you have provided for us in these posts really appreciative.
UI UX design company