 |
|||||||||
|
|||||||||
|
|
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.
