CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
craigmoliver
craigmoliver

Reputation: 6562

Create a temporary table like a current table in SQL Server 2005/2008

How do you create a temporary table exactly like a current table in a stored procedure?

Upvotes: 7

Views: 10686

Answers (4)

priyanka.sarkar
priyanka.sarkar

Reputation: 26538

A Common Table Expression or Table Variables can also server the purpose apart from a Temp Table

Upvotes: 0

HLGEM
HLGEM

Reputation: 96650

Alternatively you can script the existing table and change the name to the temp table name and add the create table script to the top of the rest of the script you want to run. I generally do this if it really important the temp table exactly match the structure of the real table (for instance when I am creating a fake table called #inserted to use when testing the code I intend to put into a trigger.)

Most of the time though the select into will get you what you need.

Upvotes: 1

manji
manji

Reputation: 47968

SELECT * INTO #t FROM table

if you want it to be empty:

SELECT * INTO #t FROM table WHERE 1 = 2

Upvotes: 8

LukLed
LukLed

Reputation: 31892

select * into #temp_table from current_table_in_stored_procedure

#temp_table - locally temp
##temp_table - globally temp

select top 0 * into #temp_table from current_table_in_stored_procedure to have empty table

Upvotes: 15

Related Questions