Execute multiple selects and store their output

Hello All,

I have to get list of allt he articles freom different databases/servers. Store the list in a table and then get record counts of each article from subscribersr and then publishers.

replsevers table has these records.
SELECT * FROM [server1].database1.dbo.Sysarticles
SELECT * FROM [server2].database2.dbo.Sysarticles
SELECT * FROM [server3].database3.dbo.Sysarticles

I have to execute these selects and store their output to a table. Then get each article and their record count from subscriber and publisher.

I have created a table listing subscirbers and publishers information.

Can anyone suggest me how to resolve my problem?

Thanks,
-B

BrookKAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
you will use a cursor as follows
declare @sql varchar(8000)
declare @stment varchar(256)
declare cr cursor for select * from replsevers

set @sql = ''

open cr
FETCH NEXT FROM cr 
INTO @stment

while @@FETCH_STATUS = 0
begin
  if @sql is null 
    set @sql = 'INSERT INTO ##OUTPUT ' + @stment
  else
    set @sql = @sql + ' UNION ALL ' + @stment

  FETCH NEXT FROM cr INTO @stment
end

EXEC(@sql)

CLOSE cr
DEALLOCATE cr

Open in new window

0
 
Ephraim WangoyaCommented:

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
0
 
BrookKAuthor Commented:
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
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
radcaesarCommented:
please explain clearly in deatil
0
 
Ephraim WangoyaCommented:

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
0
 
BrookKAuthor Commented:
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.

0
 
BrookKAuthor Commented:
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?
0
 
Ephraim WangoyaCommented:
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

Open in new window

0
 
BrookKAuthor Commented:
Thanks ewangoya.

I getting an error on UNION.

0
 
Ephraim WangoyaCommented:

Uncomment the line PRINT @sql and see what sql is being constructed
0
 
BrookKAuthor Commented:
This solution helped me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.