~Script 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.

Advertisements

Rows to string – 2 ways..

Standard

Alrighty then, new post time, more so to keep this in a place I can find it because there are a number of places that you can find how to do this, but…

A work colleague and good friend was asking for ways to convert rows to a string, there are at very least two ways to do this:

1: Using FOR XML PATH

DECLARE @Tbl TABLE
(
Name VARCHAR(20)
);

INSERT INTO @Tbl VALUES
('Jim'),
(
'Tim'),
(
'Kim');

DECLARE @ReturnVar VARCHAR(256);

SELECT STUFF((SELECT ',' + Name
                          
FROM @Tbl
                          
FOR XML PATH('')),1,1,'') AS Name;

 

2: Using COALESCE

DECLARE @Tbl TABLE
(
Name VARCHAR(20)
);

INSERT INTO @Tbl VALUES
('Jim'),
(
'Tim'),
(
'Kim');

DECLARE @ReturnVar VARCHAR(256);

SELECT @ReturnVar = COALESCE(@ReturnVar + ', ', '') + Name
FROM @Tbl;

SELECT @ReturnVar;

Pretty simple huh?