Building Tables from Temp

This week I've been handed an almost impossible task at the day job: build a database containing a subset of information from our current SQL Server-based CMS using the table structures required by the new cloud service. On the surface, this doesn't sound too complicated. So long as a person knows the data structure of both systems, SQL scripts can be written once and used multiple times. The difficult part comes down to time as there are just three working days to get this done for 100+ data tables containing as many as 300 columns of data each, and the documentation for the Cloud objects is … incomplete1.

In an effort to build as much as possible in the least amount of time, I've decided it would be best to "cheat". The first set of SQL scripts that I am writing will collect as complete a dataset as possible for each object and write to a temporary table. As this can sometimes be an iterative process to refine the output, pre-defining the data tables does not seem like a good use of time. Instead, I'd like to simply write a query in an INTO #tmpWhatever command to generate a temporary table. When I'm happy with the output, the data is then written to the new table where it will sit until exported.

Now here's the fun part. Because the data is already in a temporary table and because SQL Server makes it really easy to query table definitions, one can have the database pretty much write a table creation script for you.

This is how I do it:

SELECT '[' + col.[COLUMN_NAME] + '] ' + UPPER(col.[DATA_TYPE]) +
       CASE WHEN col.[DATA_TYPE] in ('numeric', 'nvarchar', 'varchar', 'nchar', 'char')
            THEN '(' + CASE WHEN col.[DATA_TYPE] = 'numeric' 
                            THEN CAST(col.[NUMERIC_PRECISION] as VARCHAR(3)) + ', ' + CAST(numeric_scale as VARCHAR(3))
                            WHEN col.[DATA_TYPE] IN ('nvarchar', 'varchar')
                            THEN ISNULL(CAST(col.[CHARACTER_MAXIMUM_LENGTH] as VARCHAR(4)), 'MAX')
                            WHEN col.[DATA_TYPE] IN ('nchar', 'char')
                            THEN ISNULL(CAST(col.[CHARACTER_MAXIMUM_LENGTH] as VARCHAR(4)), 'MAX')
                            END + ')'
            ELSE '' END +
       CASE WHEN col.[IS_NULLABLE] = 'NO' THEN ' NOT' ELSE '' END + ' NULL' +
       CASE WHEN col.[COLUMN_DEFAULT] IS NOT NULL THEN ' DEFAULT ' + col.[COLUMN_DEFAULT] ELSE '' END + ',' AS [ColDefinition]
  FROM [tempdb].[INFORMATION_SCHEMA].[COLUMNS] col
 WHERE col.[TABLE_NAME] LIKE '#tmpWhatever%';

This query will return as many rows as there are columns in the provided temporary table, which can then be copy/pasted into a partially-written CREATE TABLE statement. This query is going to save me hours of pain this week as I rush to complete things that should have been done weeks ago.


  1. When I use this word to describe something used in a professional setting, I mean it's untrustworthy or poorly defined. In the case of the data migration documentation, "incomplete" means both.