Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
SQL Server dynamically return specific columns from a table
Hello. I've got a table [##UDF_HCLOT_prep] that contains up to 40 columns. I'm trying to return only those columns that don't begin with 'Lot-UDF%'.
I have the following code as shown below, but when I execute it all I get back is "Invalid object name ##LotUDFs".
Any help is appreciated.
I have the following code as shown below, but when I execute it all I get back is "Invalid object name ##LotUDFs".
Any help is appreciated.
declare @sql varchar(1000)
SELECT @sql=@sql + Column_name + ','
from INFORMATION_SCHEMA.Columns
where table_name = '##UDF_HCLOT_prep' and Column_name not like '[Lot-UDF]%'
select @sql = left(@sql, len(@sql)-1)
select @sql = 'select ' + @sql + ' Into ##LotUDFs From ##UDF_HCLOT_prep'
exec (@sql)
Select * From ##LotUDFs
Asked:
-
3
2
1 Solution
declare @sql varchar(1000)
SELECT @sql=@sql + c.name + ','
from tempdb.sys.objects o
inner join tempdb.sys.columns c on c.object_id=o.object_id
where o.name = '##UDF_HCLOT_prep' and c.name not like '[Lot-UDF]%'
select @sql = left(@sql, len(@sql)-1)
select @sql = 'select ' + @sql + ' Into ##LotUDFs From ##UDF_HCLOT_prep'
exec (@sql)
Select * From ##LotUDFs
SELECT @sql=@sql + c.name + ','
from tempdb.sys.objects o
inner join tempdb.sys.columns c on c.object_id=o.object_id
where o.name = '##UDF_HCLOT_prep' and c.name not like '[Lot-UDF]%'
select @sql = left(@sql, len(@sql)-1)
select @sql = 'select ' + @sql + ' Into ##LotUDFs From ##UDF_HCLOT_prep'
exec (@sql)
Select * From ##LotUDFs
Brandon, thanks for quick reply. Because I'm using a global temp table "##" the table should remain in scope even after execution. Nonetheless, I tried your code and get:
Msg 536, Level 16, State 5, Line 7
Invalid length parameter passed to the SUBSTRING function.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'From'.
Msg 536, Level 16, State 5, Line 7
Invalid length parameter passed to the SUBSTRING function.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'From'.
Cyberwiki, thanks for quick reply. Using your code I get the following:
Msg 208, Level 16, State 1, Line 11
Invalid object name '##LotUDFs'.
Msg 208, Level 16, State 1, Line 11
Invalid object name '##LotUDFs'.
Global temp tables will remain in scope until the last process (SPID) accessing it exits. Since ##LotUDFs is only accessed by the exec(), it goes out of scope immediately upon completion.
Mine failed because I wasn't accessing tempdb.
Try:
declare @sql nvarchar(max)
SELECT @sql=isnull(@sql + ',' ,'') + '[' + Column_name + ']'
from tempdb.INFORMATION_SCHEMA.
where table_name = '##UDF_HCLOT_prep' and Column_name not like '[Lot-UDF]%'
select @sql = 'select ' + @sql + ' From med##UDF_HCLOT_prepium'
--exec sp_executesql @SQL
PRINT @SQL
Mine failed because I wasn't accessing tempdb.
Try:
declare @sql nvarchar(max)
SELECT @sql=isnull(@sql + ',' ,'') + '[' + Column_name + ']'
from tempdb.INFORMATION_SCHEMA.
where table_name = '##UDF_HCLOT_prep' and Column_name not like '[Lot-UDF]%'
select @sql = 'select ' + @sql + ' From med##UDF_HCLOT_prepium'
--exec sp_executesql @SQL
PRINT @SQL
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
declare @sql nvarchar(max)
set @SQL = ''
SELECT @sql=@sql + '[' + Column_name + '],'
from INFORMATION_SCHEMA.Columns
where table_name = '##UDF_HCLOT_prep' and Column_name not like '[Lot-UDF]%'
select @sql = left(@sql, len(@sql)-1)
select @sql = 'select ' + @sql + ' From ##UDF_HCLOT_prep'
exec sp_executesql @SQL