CODEWITHSUNDEEP
sqlsql-servert-sql
Brakkie101
Brakkie101

Reputation: 169

Multiple Linked Servers in one select statement with one where clause, possible?

Got a tricky one today (Might even just be me):

I have 8 Linked SQL 2012 servers configured to my main SQL server and I need to create table views so that I can filter all these combined table results only using one where clause, currently I use UNION because they all have the same table structures.

Currently my solution looks as follows:

SELECT * FROM [LinkedServer_1].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_2].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_3].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_4].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_5].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_6].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_7].[dbo].[Table] where value = 'xxx'
UNION
SELECT * FROM [LinkedServer_8].[dbo].[Table] where value = 'xxx'

As you can see this is becoming quite ugly because I have a select statement and where clause for each linked server and would like to know if there was a simpler way of doing this!

Appreciate the feedback.

Brakkie101

Upvotes: 2

Views: 521

Answers (1)

gotqn
gotqn

Reputation: 43646

Instead of using views, you can use inline table-valued functions (a view with parameters). It will not save initial efforts for creating the queries, but could save some work in the future:

CREATE FUNCTION [dbo].[fn_LinkedSever] (@value NVARCHAR(128))
AS
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM [LinkedServer_1].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_2].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_3].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_4].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_5].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_6].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_7].[dbo].[Table] where value = @value
    UNION
    SELECT * FROM [LinkedServer_8].[dbo].[Table] where value = @value
);

Also, if possible, use UNION ALL instead of UNION.

Upvotes: 2

Related Questions