Solved

SQL Server dynamically return specific columns from a table

Posted on 2010-09-13
6
383 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
  • 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now