Execute multiple selects and store their output

insert into output
SELECT * FROM [server1].database1.dbo.Sysarticles
union all
SELECT * FROM [server2].database2.dbo.Sysarticles
union all
SELECT * FROM [server3].database3.dbo.Sysarticles

assuming all the tables have the same number of fields otherwise you have to list the filed names inthe select query

You can then query the output table

These selects are in the table replsevers
If I do - SELECT * FORM replsevers
result-
SELECT * FROM [server1].database1.dbo.Sysarticles
SELECT * FROM [server2].database2.dbo.Sysarticles
SELECT * FROM [server3].database3.dbo.Sysarticles
Do I run cursor to execute each select

please explain clearly in deatil

If you mean the actual record is the select statement, then you do have to use a cursor to iterate the records while constructing a sql statement

I have to see if the record counts are matching between subscribers and publications.

We have server1 as subsriber to server2 (publisher)
server2 subscriber to server3 (publisher)

I wanted to write SQL and execute it weekly to get these record counts. But I do not want to hard code any servers, aticles or filters.

So I have created a table with the list of publishers, databases and subscribers in Replications table. Now using that list I am building selects into a table replsevers. Like this -
INSERT INTO replsevers
SELECT 'SELECT * FROM ' + PublicationServer + '.'+PublicationDatabase + '.dbo.Sysarticles'
FROM Replications.

So this is the reault from replsevers table-
SELECT * FROM [server1].database1.dbo.Sysarticles
SELECT * FROM [server2].database2.dbo.Sysarticles
SELECT * FROM [server3].database3.dbo.Sysarticles

Now I have to execute these records and store output in a temp table. And then run SELECT COUNT(*) FROM article WHERE filter to get record counts from subscribers and publishers.

I hope this is clear now. Please let me know if need further explanation.

I am writing a cursor. I thought I can eliminate the cursor.

If I have to upload only the dest_tabl and filter_clause columns from the execution of selects (SELECT * FROM [server1].database1.dbo.Sysarticles) and two column variables from cursor (Fetch next into @var1 and @var2) to the ##OUTPUT table then how do I write the INSERT INTO clause?

you can try this

declare @sql varchar(8000)
declare @tablename varchar(128), @whereclause varchar(128)
declare cr cursor for select * from replsevers

set @sql = ''

open cr
FETCH NEXT FROM cr 
INTO @tablename, @whereclause

while @@FETCH_STATUS = 0
begin
  if @sql is null 
    set @sql = 'INSERT INTO ##OUTPUT SELECT * FROM ' + @tablename + ' WHERE ' + @whereclause
  else
    set @sql = @sql + ' UNION ALL SELECT * FROM ' + @tablename + ' WHERE ' + @whereclause

  FETCH NEXT FROM cr INTO @tablename, @whereclause
end

--PRINT @sql
EXEC(@sql)

CLOSE cr
DEALLOCATE cr

Select allOpen in new window

Thanks ewangoya.

I getting an error on UNION.

Uncomment the line PRINT @sql and see what sql is being constructed

This solution helped me.