July 4, 2011 at 7:47 am
Hi I am running 10 cte select statement against 10 linked servers and combining the results with a series of 10 union statemtents like:
with server1 as
(
select * from openquery
(
linked_server1,
'SELECT * from tbl1'
)
),
server2 as
(
select * from openquery
(
linked_server2,
'SELECT * from tbl2'
)
),
server3 as
(
select * from openquery
(
linked_server3,
'SELECT * from tbl3'
)
),
union_cte as
(
select * from server1
union all
select * from server2
union all
select * from server3
)
select * from union_cte
This seems very static to me, is there a better way that I can create a list of all linked server names and iterate through that list rather than repeating the same cte 10 times for each linked server?
July 4, 2011 at 8:47 am
You could also use Dynamic SQL for the task but it seems like a waste of resources to incur the overhead of looking up the list of linked servers and building a SELECT every time you need it. When you add a new linked server why not just modify a VIEW that contains your SELECT...UNION ALL statements and then refer to the VIEW for everything?
As an aside you could have written your SQL like this:
SELECT *
FROM OPENQUERY(linked_server1, 'SELECT * from tbl1')
UNION ALL
SELECT *
FROM OPENQUERY(linked_server2, 'SELECT * from tbl2')
UNION ALL
SELECT *
FROM OPENQUERY(linked_server3, 'SELECT * from tbl3') ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 4, 2011 at 11:40 am
Hi opc.three thank you for your reply, your suggestion makes sense and makes it simpler. As a learning excercise if I were to use dynamic sql how would this be implemented? ie,
declare @servers as nvarchar(1000)
set @servers = 'server1, server2, server3'
select *
from
openquery
(
'SELECT * from tbl1'
)
July 6, 2011 at 8:59 am
Keep in mind I haven't tested this solution, since I have no linked servers on the server I'm currently using. But, this should work. Basically, you're looping through each linked server in sys.servers, and executing the OPENQUERY statement, and inserting the rows into the temp table you created.
CREATE TABLE #ServerResults
(
[Columns]
)
DECLARE @CurrID INT
DECLARE @NewID INT
DECLARE @LinkedServerName NVARCHAR(MAX)
DECLARE @DynamicSQL NVARCHAR(MAX)
SET @CurrID = 0
SET @NewID = 0
WHILE 1 = 1
BEGIN
SET @LinkedServerName = ''
SET @DynamicSQL = ''
SELECT TOP 1
@LinkedServerName = [name],
@NewID = server_id
FROM sys.servers
WHERE is_linked = 1
ORDER BY server_id
IF @CurrID = @NewID
BREAK
IF LEN(@LinkedServerName) > 0
BEGIN
SET @DynamicSQL =
'INSERT INTO #ServerResults ([Columns]
SELECT [Columns]
FROM OPENQUERY(' + @LinkedServerName + ', ''SELECT [Columns] from tbl1'')'
EXEC (@DynamicSQL)
END
SET @CurrID = @NewID
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply