With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.
Course Of The Month
7 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Server dynamically return specific columns from a table
Posted on 2010-09-13
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
6 Comments
That is because when you execute SQL using exec(@SQL), the temp table goes out of scope once the execution is complete. Try this...
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
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
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
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:
ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Suggested Courses
Course of the Month7 days, 2 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
724 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.