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
DROP TABLE dbo.Product;
CREATE TABLE dbo.Product
ProductId INTEGER IDENTITY(1,1),
CreatedDate DATETIME DEFAULT GETDATE()
INSERT INTO dbo.Product VALUES ('Cogs'),('Widgets'),('Spoolers'),('Sprockets'),('Openers'),('Closers'),('Silencers'),('Speakers');
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
+ ' '
+ CASE WHEN t.name LIKE '%char%' THEN '(' + CAST(t.max_length AS VARCHAR(4)) + ')' ELSE '' END
+ IIF(t.is_nullable = 1, ' NULL,', ' NOT NULL,')
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 '
+ '(' + CHAR(10)
+ LEFT(@Definition,LEN(@Definition) - 2)
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.