Cloning a temp table schema in SQL

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/cloning_a_temp_table_schema_in_sql.htm]

SQL temp tables can be a functional and performance life-saver because they let you store intermediate results. You can then do resource-heavy calculations on this smaller result set, or just break a complex procedure into multiple, easier-to-handle parts.  Sometimes you'll have two temp tables with the same schema. SQL provides as easy way to clone a temp table (thanks to our Paylocity SQL architect for showing me this). Here's a sample:

CREATE TABLE #Main1 --prePaging
(
  colInt int,
  colString varchar(20)
)

Insert Into #Main1 Values(10, 'aaa')
Insert Into #Main1 Values(20, 'bbb')
Insert Into #Main1 Values(30, 'ccc')

--Dynamically create a close of #Main1, call it #Main2
--This doesn't touch data
Select * Into #Main2 From #Main1 Where 1=2

--show that Main2 exists, and has the right schema
Select * From #Main1
Select * From #Main2

--Now insert some data into Main2
Insert Into #Main2 (colInt, colString) Values(40, 'ddd')
Select * From #Main2

drop table #Main1
drop table #Main2

The key is the line "Select * Into #Main2 From #Main1 Where 1=2", which dynamically creates table Main2 and sets its columns equal to that of Main1.

 

---

Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.