Friday, August 31, 2012

Create table from stage table on the fly

If you have a script that does nightly drop and recreate of tables, your drop query should look something like this:
IF OBJECT_ID('dbo.StageTable', 'U') IS NOT NULL
  DROP TABLE dbo.StageTable;
What if you have to create a bunch of destination tables from stage table on the fly? You can use:
IF OBJECT_ID('dbo.DestinationTable') IS NULL
  SELECT * INTO dbo.DestinationTable FROM dbo.StageTable WHERE 1=0
The above creates the table structure with no data in it and you can then do your inserts as necessary. This is particularly useful when you have to wade through thousands of tables in a database using the GUI, select desired table and generate a create script for it.