• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

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

0
BrookK
Asked:
BrookK
  • 5
  • 5
1 Solution
 
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
 
radcaesarCommented:
please explain clearly in deatil
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
Ephraim WangoyaCommented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now