Dynamically creating a @table Table from a current table

Question

Dynamically creating a @table Table from a current table

Lee Hopkins

SSC Eights!

Points: 960
More actions
September 27, 2016 at 8:57 am

#313943

I have a script that gives me a string of the columns and types.
I wan to use this result to create a declared table
DECLARE @listStr VARCHAR(Max)
SELECT @listStr = COALESCE(@liststr+', ','') +
COLUMN_NAME + ' ' + Data_type + ' ' +
CASE WHEN data_type = 'char' THEN '('+ cast(character_maximum_length AS VARCHAR) +')' ELSE '' END + ' ' +
CASE WHEN is_nullable = 'NO' THEN 'NOT NULL' ELSE '' END
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TEST'
SELECT @listStr
DECLARE @table1 TABLE(@liststr)
How can i get my @liststr in to the @Table1
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The only way you will be able to do this is to dynamically create a table data type and then use that declare your table variable.

Lee Hopkins SSC Eights! Points: 960 More actions · Answer 2

Lee Hopkins

SSC Eights!

Points: 960

September 27, 2016 at 9:58 am

#1903398

Table Data Type.?

ScottPletcher SSC Guru Points: 101097 More actions · Answer 3

I can't think of a way right now for that to work using a table variable.

Can you use a temp table instead?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Building off of what Scott said - a table variable is not the best choice for what you are doing because it can't be referenced outside of the dynamic SQL batch. Consider the following statements and my notes:

-- This will fail because @x only exists while the EXEC statement below is running

EXEC ('DECLARE @x TABLE(col1 int); INSERT @x VALUES (1);')

SELECT * FROM @x;

-- This works because ##x is a global temp table & exists after the EXEC statement is done.

EXEC ('CREATE TABLE ##x(col1 int); INSERT ##x VALUES (1);')

SELECT * FROM ##x;

As you can see, a global temp table will be better. The only way to use a table variable is if everything you need happens inside of the EXEC statement. Consider this query:

SET NOCOUNT ON;

USE tempdb

GO

-- a real table for testing

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

IF OBJECT_ID('dbo.test') IS NULL

BEGIN

CREATE TABLE dbo.test (col1 varchar(100), col2 int);

INSERT dbo.test VALUES ('xxx',1),('abc',2);

END

-- variable to hold the name of the table you want to work with

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

DECLARE @yourTable varchar(100) = 'test';

-- using your code to get a list of columns

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

DECLARE @listStr VARCHAR(Max)

SELECT @listStr = COALESCE(@liststr+', ','') +

COLUMN_NAME + ' ' + Data_type + ' ' +

CASE

WHEN data_type IN ('char', 'varchar')

THEN '('+ cast(character_maximum_length AS VARCHAR) +')' ELSE ''

END +' '+

CASE

WHEN is_nullable = 'NO'

THEN 'NOT NULL' ELSE 'NULL'

END

FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @yourTable;

-- Dynamic SQL statement to create your table

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

DECLARE @sql varchar(2000) = 'DECLARE @table1 TABLE('+@listStr+');'

SET @sql += 'INSERT @table1 SELECT * FROM '+@yourTable+'; SELECT * FROM @table1';;

PRINT @sql;

EXEC (@sql);

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Lee Hopkins SSC Eights! Points: 960 More actions · Answer 5

Lee Hopkins

SSC Eights!

Points: 960

September 27, 2016 at 11:15 am

#1903420

Alan B. Thanks make sense.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Only issue I see with the global temporary table would be if the procedure is called at the same time by multiple sessions.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Also, a slightly more robust way to get the column names and and data types:

declare @collist nvarchar(max),

@schema nvarchar(128),

@table nvarchar(128);

set @schema = N'dbo';

set @table = N'Test';

select @collist = STUFF((select N', ' + col.name + N' ' + typ.name +

case when typ.name in (N'nchar',N'char',N'binary') then '(' + cast(col.max_length as varchar) + ')'

when typ.name in (N'nvarchar',N'varchar',N'varbinary') then

case when col.max_length = -1 then N'(max)'

else case when typ.name in (N'varchar',N'varbinary') then N'(' + cast(col.max_length as nvarchar) + N')'

else N'(' + cast(col.max_length/2 as nvarchar) + N')'

end

when typ.name in (N'numeric',N'decimal') then N'(' + cast(typ.precision as nvarchar) + N',' + cast(typ.scale as nvarchar) + N')'

when typ.name in (N'float',N'real') then N'(' + cast(typ.precision as nvarchar) + N')'

when typ.name in (N'datetime2',N'datetimeoffset',N'time') then '(' + cast(typ.scale as nvarchar) + N')'

else ''

end

from

sys.schemas sch

inner join sys.tables tab

on (sch.schema_id = tab.schema_id)

inner join sys.columns col

on (tab.object_id = col.object_id)

inner join sys.types typ

on (col.system_type_id = typ.system_type_id and col.user_type_id = typ.user_type_id)

where

sch.name = @schema and

tab.name = @table

order by

col.column_id

for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'');

select @collist;

Ignore this code, replace issue modifying the code;

Lee Hopkins SSC Eights! Points: 960 More actions · Answer 8

much more robust but for some reason one of my columns equUK [decimal] [14,0] is coming back as equUK decimal(38,38).

ahh found it. had to change the typ.percision to col.percision and tpy.scale to col.scale

when typ.name in (N'numeric',N'decimal') then N'(' + cast(col.precision as nvarchar) + N',' + cast(col.scale as nvarchar) + N')'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Lee Hopkins (9/27/2016)
much more robust but for some reason one of my columns equUK [decimal] [14,0] is coming back as equUK decimal(38,38)

I know why, replace error on my part as I was copying the code from a function I wrote. Hang on.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Try this one:

declare @collist nvarchar(max),

@schema nvarchar(128),

@table nvarchar(128);

set @schema = N'dbo';

set @table = N'Test';

select @collist = STUFF((select N', ' + col.name + N' ' + typ.name +

case when typ.name in (N'nchar',N'char',N'binary') then '(' + cast(col.max_length as varchar) + ')'

when typ.name in (N'nvarchar',N'varchar',N'varbinary') then

case when col.max_length = -1 then N'(max)'

else case when typ.name in (N'varchar',N'varbinary') then N'(' + cast(col.max_length as nvarchar) + N')'

else N'(' + cast(col.max_length/2 as nvarchar) + N')'

end

when typ.name in (N'numeric',N'decimal') then N'(' + cast(col.precision as nvarchar) + N',' + cast(col.scale as nvarchar) + N')'

when typ.name in (N'float',N'real') then N'(' + cast(col.precision as nvarchar) + N')'

when typ.name in (N'datetime2',N'datetimeoffset',N'time') then '(' + cast(col.scale as nvarchar) + N')'

else ''

end

from

sys.schemas sch

inner join sys.tables tab

on (sch.schema_id = tab.schema_id)

inner join sys.columns col

on (tab.object_id = col.object_id)

inner join sys.types typ

on (col.system_type_id = typ.system_type_id and col.user_type_id = typ.user_type_id)

where

sch.name = @schema and

tab.name = @table

order by

col.column_id

for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'');

select @collist;

ScottPletcher SSC Guru Points: 101097 More actions · Answer 11

You don't need a global temp table, a regular temp table will do.

Create the table in main stored proc, then ALTER it using dynamic SQL. Since the temp table already exists, the dynamic SQL is basically a sub-proc of the main proc, and it can "see" the temp table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

Lee Hopkins (9/27/2016)
I have a script that gives me a string of the columns and types.
I wan to use this result to create a declared table
DECLARE @listStr VARCHAR(Max)
SELECT @listStr = COALESCE(@liststr+', ','') +
COLUMN_NAME + ' ' + Data_type + ' ' +
CASE WHEN data_type = 'char' THEN '('+ cast(character_maximum_length AS VARCHAR) +')' ELSE '' END + ' ' +
CASE WHEN is_nullable = 'NO' THEN 'NOT NULL' ELSE '' END
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TEST'
SELECT @listStr
DECLARE @table1 TABLE(@liststr)
How can i get my @liststr in to the @Table1

More to the point, why do you want to do this? It seems like you have decided the best way to do something, but don't know how to do it, so maybe it's not the best way.

Can you describe the actual problem this will help you to solve?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

mister.magoo (9/27/2016)
Lee Hopkins (9/27/2016)
I have a script that gives me a string of the columns and types.
I wan to use this result to create a declared table
DECLARE @listStr VARCHAR(Max)
SELECT @listStr = COALESCE(@liststr+', ','') +
COLUMN_NAME + ' ' + Data_type + ' ' +
CASE WHEN data_type = 'char' THEN '('+ cast(character_maximum_length AS VARCHAR) +')' ELSE '' END + ' ' +
CASE WHEN is_nullable = 'NO' THEN 'NOT NULL' ELSE '' END
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TEST'
SELECT @listStr
DECLARE @table1 TABLE(@liststr)
How can i get my @liststr in to the @Table1
More to the point, why do you want to do this? It seems like you have decided the best way to do something, but don't know how to do it, so maybe it's not the best way.
Can you describe the actual problem this will help you to solve?

True, if you want to create a temporary table based on an existing table there is a simple way:

select * into #temptable

from dbo.test

where 1 = 2;

Lee Hopkins SSC Eights! Points: 960 More actions · Answer 14

I am needing to compare the data from 2 different locations.

I have a openrowset to a remote location i bring back the null values when i do a left join from homeoffice to remote sites. and these i have to then compare all columns case hqx = remotex then 't' else 'f' for all tables. since i have over 97 tables i can not manually create each comparison. i have any where from 9-45 columns i need to match up. i have all the others stuff working but i needed a way to dynamically create these temp tables.