INSERT INTO & Temp Table Creation

At my previous employer, my old boss and I would debate the merits of using “INSERT INTO” to create temp tables. He was against it in all cases, where I would use it when I was exploring a problem, or exploring a solution. I had always tried to not use it in production code, but apparently he recently found evidence of my code used in production.

As proof of my good intentions for never using “INSERT INTO” in production code, yet seeing the value in being able to write a query to return a dataset many columns wide, and then be able to turn it into a table in a few keystrokes, I share this stored procedure with the world.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[UTIL].[CreateStatementForTempTable]') AND OBJECTPROPERTY(Object_id, N'IsProcedure') = 1)
DROP PROCEDURE [UTIL].[CreateStatementForTempTable]
GO

CREATE PROCEDURE [UTIL].[CreateStatementForTempTable]
(
@TempTableName VARCHAR(1000)
)
AS
SET NOCOUNT ON

-- Purpose: Pass in temp table, return create table statement
-- Date: 10/5/2009
-- Version: 1.00.000
-- Author: Matt Silva

/* Notes:
Laziness is the mother of all invention.
*/


-------------------------------------------------------------------------------

DECLARE @SQLToRun VARCHAR(MAX)

-------------------------------------------------------------------------------

IF @TempTableName IS NULL
RETURN

-------------------------------------------------------------------------------

SET @SQLToRun = '
;
WITH CTE_OutputSQL AS (
SELECT ''IF OBJECT_ID(''''tempdb..' + @TempTableName + ''''') IS NOT NULL'' AS [Statement], -4 AS OrderBy
UNION
SELECT ''   DROP TABLE ' + @TempTableName + ''' AS [Statement], -3 AS OrderBy
UNION
SELECT '''' AS [Statement], -2 AS OrderBy
UNION
SELECT ''CREATE TABLE ' + @TempTableName + ''' AS [Statement], -1 AS OrderBy
UNION
SELECT ''('' AS [Statement], 0 AS OrderBy
UNION
SELECT
CASE
WHEN C.ORDINAL_POSITION = 1 THEN ''''
ELSE '', ''
END
+ QUOTENAME(c.COLUMN_NAME) + SPACE(1) +
CASE
WHEN C.DATA_TYPE = ''bigint'' THEN ''bigint''
WHEN C.DATA_TYPE = ''binary'' THEN ''binary''
WHEN C.DATA_TYPE = ''bit'' THEN ''bit''
WHEN C.DATA_TYPE = ''char'' THEN ''char('' + CAST( C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''date'' THEN ''date''
WHEN C.DATA_TYPE = ''datetime'' THEN ''datetime''
WHEN C.DATA_TYPE = ''datetimeoffset'' THEN ''datetimeoffset''
WHEN C.DATA_TYPE = ''datetime2'' THEN ''datetime2''
WHEN C.DATA_TYPE = ''decimal'' THEN ''decimal('' + CAST( C.NUMERIC_PRECISION AS VARCHAR(MAX) ) + '', '' + CAST( C.NUMERIC_SCALE AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''float'' THEN ''float''
WHEN C.DATA_TYPE = ''image'' THEN ''image''
WHEN C.DATA_TYPE = ''int'' THEN ''int''
WHEN C.DATA_TYPE = ''money'' THEN ''money''
WHEN C.DATA_TYPE = ''numeric'' THEN ''numeric('' + CAST( C.NUMERIC_PRECISION AS VARCHAR(MAX) ) + '', '' + CAST( C.NUMERIC_SCALE AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''nchar'' THEN ''nchar('' + CAST( C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''ntext'' THEN ''ntext('' + CAST( C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''nvarchar'' THEN ''nvarchar('' + CAST( C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''real'' THEN ''real''
WHEN C.DATA_TYPE = ''smalldatetime'' THEN ''smalldatetime''
WHEN C.DATA_TYPE = ''smallint'' THEN ''smallint''
WHEN C.DATA_TYPE = ''smallmoney'' THEN ''smallmoney''
WHEN C.DATA_TYPE = ''text'' THEN ''text''
WHEN C.DATA_TYPE = ''time'' THEN ''time''
WHEN C.DATA_TYPE = ''timestamp'' THEN ''timestamp''
WHEN C.DATA_TYPE = ''tinyint'' THEN ''tinyint''
WHEN C.DATA_TYPE = ''uniqueidentifier'' THEN ''uniqueidentifier''
WHEN C.DATA_TYPE = ''varbinary'' THEN ''varbinary''
WHEN C.DATA_TYPE = ''varchar'' THEN ''varchar('' + CAST( C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + '')''
WHEN C.DATA_TYPE = ''sql_variant'' THEN ''sql_variant''
WHEN C.DATA_TYPE = ''xml'' THEN ''xml''
ELSE ''''
END  AS [Statement], C.ORDINAL_POSITION AS OrderBy
FROM tempdb.INFORMATION_SCHEMA.tables t
INNER JOIN tempdb.sys.tables ta ON t.TABLE_NAME = ta.NAME
INNER JOIN tempdb.INFORMATION_SCHEMA.columns c ON ta.NAME = c.TABLE_Name
WHERE ta.OBJECT_ID = OBJECT_ID(''tempdb..' + @TempTableName + ''')
UNION
SELECT '')'' AS [Statement], 99999 AS OrderBy
)
SELECT [Statement]
FROM CTE_OutputSQL
ORDER BY OrderBy
'

EXEC( @SQLToRun )

SET NOCOUNT OFF
RETURN
GO

It contains all of the datatypes for SQL Server 2005 and SQL Server 2008. Basic usage as follows:

SELECT
/* Many, Many, Many columns */
INTO #<temp_table_name>
FROM
/* A nice long series of tables */

EXEC [UTIL].[CreateStatementForTempTable] '#<temp_table_name>'

The result set will return a CREATE TABLE statement that matches the columns that were in the temp table created by the INSERT INTO statement.

You can use the result set to tweak the table if it doesn’t match the datatype you expect it to (I haven’t seen that happen) or if you wanted to add NOT NULL or other constraints.

This entry was posted in SQL Server Development and tagged , , , . Bookmark the permalink.

2 Responses to INSERT INTO & Temp Table Creation

  1. Pingback: SQL Server: Temporary Table Scripting | I grok, therefore I am

  2. Joshua Lynn says:

    Credit where credit is due. I used this sproc again today.

    tnx Matt

Leave a Reply

Your email address will not be published. Required fields are marked *