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.