How do you create a temporary table exactly like a current table in a stored procedure?
+9
A:
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
LukLed
2009-11-06 19:06:28
This will copy the data from current_table into a #temp_table, but #temp_table will not have the same keys, identity settings, etc. Still, this is the accepted way to perform this task.
David Lively
2009-11-06 19:08:02
@David: the temp table *will* inherit identity settings, but no, none of the keys or indexing. I wish I could turn it off!
Peter
2009-11-06 19:16:52
+6
A:
SELECT * INTO #t FROM table
if you want it to be empty:
SELECT * INTO #t FROM table WHERE 1 = 2
najmeddine
2009-11-06 19:07:59
+1
A:
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.
HLGEM
2009-11-06 19:18:14
A:
A Common Table Expression or Table Variables can also server the purpose apart from a Temp Table
priyanka.sarkar
2009-11-07 05:11:01