Retrieving Temp Table Definition

Standard

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:

TempTableDefinition

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.

SSIS – Insert fails on column with a default value

Standard

Adding a ‘Created On’ field is a great idea for determining when a particular record was created in your data warehouse. This can help with a number of things not least of which is trouble shooting.

I recently added a new CreatedOn field with a default of GETDATE(), however I purposefully didn’t hook this up in my ETL so that it would automatically be populated by SQL Server itself.

I quickly ran into a problem when the package used to populate this table started failing with the below error:

“An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Cannot insert the value NULL into column ‘CreatedOn’, table ‘Sales.fact.Package’; column does not allow nulls. INSERT fails.”.”

I checked my package and this field wasn’t hooked up, so what was going on? It should have been getting a NULL inserted and then SQL Server should have put in a value of GETDATE()?!!

The problem was with the Keep Nulls check box in the OLEDB Destination:

DefaultGetdate

SSIS will override the default on the column upon insert. Because I don’t need to keep any of the nulls unchecking this check box solved the problem.

There’s a good article here on www.sqlservercentral.com with a bit more information.bit more information.