Solved

SQL Server dynamically return specific columns from a table

Posted on 2010-09-13
6
395 Views
Last Modified: 2012-05-10
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.
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

Open in new window

0
Comment
Question by:jobprojn
[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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33666709
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


0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33666715
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
0
 

Author Comment

by:jobprojn
ID: 33666806
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'.
0
U.S. Department of Agriculture and Acronis Access

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.

 

Author Comment

by:jobprojn
ID: 33666814
Cyberwiki, thanks for quick reply.  Using your code I get the following:

Msg 208, Level 16, State 1, Line 11
Invalid object name '##LotUDFs'.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 33666860
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.Columns
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


0
 

Author Closing Comment

by:jobprojn
ID: 33666920
Worked, thanks much.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

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…

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.

Join & Ask a Question