0

I have a query that I need to run over 4 different DB's.

The query is the same with the only the DB name changing. Something in the order of:

FOR @db IN ('db1','db2','db3','db4') LOOP
 EXECUTE QUERY FROM @db
END

The results should be a union off all 4 DB's.

Antonio
  • 81
  • 9

1 Answers1

0

I think you are going to need to use dynamic SQL to build a SQL script than you can then execute at the end. Something like:

declare @dblist as table (upk int identity(1,1), dbname varchar(100))

insert into @dblist (dbname)
Select 'db1' UNION ALL 
Select 'db2' UNION ALL
Select 'db3' UNION ALL
Select 'db4'

Declare @mincount int, @maxcount int,  @script varchar(max), @dbame varchar(100)

Set @mincount=1
Set @maxcount=(Select max(upk) from @dblist)

While @mincount<=@maxcount
BEGIN 
Set @dbname=(select dbname from @dblist where upk=@mincount)
Set @script =@script + ' Select * from ' + @dbname + ' UNION ALL '
Set @mincount=@mincount+1
END

Set @script=left(@script, len(@script)-9)

--Print @Script
Exec(@script)
iainc
  • 862
  • 6
  • 20