August 11, 2009 at 10:04 pm
Hi SQL experts:
I am using SQL2008 and wondering how to loop through tables with dynamic names?
I have a list that consist of random characters:
xyz,abc,a12,...
from those random #s, I can retrive tableid fields
e.g. select tableid from tablelist where prod in ('xyz','abc','a12'...)
result of tableid would be a list of random numbers like:
135,235,345,356,378...
I then need to loop through a list of tables with this tableid list with those number, then select fields out of those tables:
tbl135,tbl235,tbl345,tbl356,tbl378....
How do I set up a loop through the tables dynamically?
so that I could repeat the select * from tbl### and put the output into a temp table
by doing each select * from tbl### into #mytmp ?
My SQL knowledge is limited and it's too much pain to go through the list and each selects when the list gets long.
Thanks a lot for any help provided!
August 12, 2009 at 9:49 am
Could someone shed some light for me please?
Basically my question is how to dynamically loop through a list of tables?
e.g. select * into #mytemp from tablexxx
where I have a list of xxx coming in (123,235,345...)
August 12, 2009 at 10:37 am
Here is an example to use to give you an idea of what can do to achieve desired result . Also, Please read the link in my signature for tips on how to receive better help.
declare @sql nvarchar(max)
declare @dbname varchar(255)
declare @cnt int
select @cnt = 1
declare @Tmp table(cnt int, dbid int, dbname varchar(255))
insert into @Tmp
select row_number() over (order by database_id) as cnt ,database_id, name from sys.databases
while @cnt < (select MAX(cnt) from @Tmp)+1
begin
set @dbname = (select dbname from @Tmp where cnt = @cnt)
set @sql = 'select object_id,name from '+@dbname+'.sys.tables'
--print @sql
exec sp_executesql @sql
set @cnt = @cnt + 1
end
August 12, 2009 at 5:27 pm
Thanks for the reply!
Sorry that I couldn't quite make out of the example. My ### is random as tbl###.
August 13, 2009 at 7:52 am
Did you read the link in my signature? If not, please do so then post example tables and data and I may be able to help you further.
in any case here are a few tips to help you get going.
this line in the example
select row_number() over (order by database_id) as cnt ,database_id, name from sys.databases
is what you need to do with
select tableid from tablelist where prod in ('xyz','abc','a12'...)
then in the dynamic sql portion
this line:
set @sql = 'select object_id,name from '+@dbname+'.sys.tables'
would be replaced with something like this
set @sql = 'select * from tbl'+@tableid
August 13, 2009 at 6:32 pm
I got it now. Thanks a lot for your help, Bob!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply