Today I was creating a temp table using a SELECT * INTO in a stored procedure that was being used a source for an SSIS load package. Of course when plugged the stored proc into my load package it gave the below error:
This is easily resolved by using WITH RESULT SETS. Now my problem was that my temp table was quite wide and would have taken ages to manually create the definition to use in my result sets. “Tell us Jim what’s the solution?!?!?! I can hear you screaming. Well it’s off to the sys.objects table in Tempdb where our temporary table is stored.
Firstly let’s create some test data and insert it into a temp table:
IF OBJECT_ID('dbo.Product') IS NOT NULL BEGIN DROP TABLE dbo.Product; END CREATE TABLE dbo.Product ( ProductId INTEGER IDENTITY(1,1), ProductName VARCHAR(200), CreatedDate DATETIME DEFAULT GETDATE() ); INSERT INTO dbo.Product VALUES ('Cogs'),('Widgets'),('Spoolers'),('Sprockets'),('Openers'),('Closers'),('Silencers'),('Speakers'); SELECT * INTO #Product FROM dbo.Product
Now that we’ve got a temp table in tempdb let’s get it’s definition:
--Create a variable to hold the name of the temp table and one to hold the resulting definition DECLARE @TableName NVARCHAR(256) = '#Product', @Definition NVARCHAR(1000) = ''; --Now build the columns: SELECT @Definition = @Definition + c.Name + ' ' + UPPER(t.name) + CASE WHEN t.name LIKE '%char%' THEN '(' + CAST(t.max_length AS VARCHAR(4)) + ')' ELSE '' END + IIF(t.is_nullable = 1, ' NULL,', ' NOT NULL,') + CHAR(10) FROM tempdb.sys.columns c INNER JOIN tempdb.sys.types t ON t.system_type_id = c.system_type_id WHERE OBJECT_ID = OBJECT_ID('tempdb..' + @TableName) ORDER BY c.column_id; --Now build the create table startement SELECT 'CREATE TABLE ' + @TableName + '(' + CHAR(10) + LEFT(@Definition,LEN(@Definition) - 2) + CHAR(10) + ')';
When you run the above code you get out the table definition of our temp table:
CREATE TABLE #Product ( ProductId INT NULL, ProductName VARCHAR(8000) NULL, CreatedDate DATETIME NULL )
It’s a little rough at the moment as our varchar column was originally varchar(200) so it does need a little bit of tweaking, but it was sufficient for what I needed.