September 27, 2016 at 8:57 am
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
September 27, 2016 at 9:51 am
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.
September 27, 2016 at 9:58 am
Table Data Type.?
September 27, 2016 at 10:32 am
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".
September 27, 2016 at 10:45 am
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);
-- Itzik Ben-Gan 2001
September 27, 2016 at 11:15 am
Alan B. Thanks make sense.
September 27, 2016 at 11:47 am
Only issue I see with the global temporary table would be if the procedure is called at the same time by multiple sessions.
September 27, 2016 at 11:50 am
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
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;
September 27, 2016 at 12:05 pm
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')'
September 27, 2016 at 12:08 pm
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.
September 27, 2016 at 12:10 pm
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
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;
September 27, 2016 at 1:59 pm
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".
September 27, 2016 at 2:25 pm
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);September 27, 2016 at 2:53 pm
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;
September 27, 2016 at 4:06 pm
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.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply