UNION from many linked servers

Question

Post reply

UNION from many linked servers

nkat

SSChasing Mays

Points: 654

June 20, 2022 at 12:22 pm

#4052113

Hello!

Here is an example of a script that selects into one table the same data spread across 2 linked servers

--/****** Script for SelectTopNRows command from SSMS  ******/
DECLARE @DateTimeStart DATETIME;
DECLARE @DateTimeEnd DATETIME;

SET @dateTimeStart= '2022-06-10 10:00:00'
SET @dateTimeEnd= '2022-06-10 11:00:00';


IF OBJECT_ID(N'tempdb..#TQC') IS NOT NULL
BEGIN
DROP TABLE #TQC
END

  SELECT 
cntQUEUE as cntQUEUE
,cntDate as cntDate
  ,cntSHEETSTOTAL as cntSHEETSTOTAL
  ,cntSHEETSCOLOR as cntSHEETSCOLOR
INTO #TQC
  FROM [w].[dbo].[qc]
  WHERE cntDATE BETWEEN @DateTimeStart AND @DateTimeEnd
  UNION 
  SELECT cntQUEUE as cntQUEUE
  ,cntDate as cntDate
  ,cntSHEETSTOTAL as cntSHEETSTOTAL
  ,cntSHEETSCOLOR as cntSHEETSCOLOR
  FROM [s00-6800-be01\w].[w].[dbo].[qc]
  WHERE cntDATE BETWEEN @DateTimeStart AND @DateTimeEnd
 
SELECT * FROM #TQC
ORDER BY cntQUEUE

I've got about 40 servers to link the data from

So, is there a way to save the effort and write smth like

@TABLELIST = '[s00-0001-be01\w].[w].[dbo].[qc]','[s00-0002-be01\w].[w].[dbo].[qc]','[s00-0040\w].[w].[dbo].[qc]'
 SELECT cntQUEUE as cntQUEUE
  ,cntDate as cntDate
  ,cntSHEETSTOTAL as cntSHEETSTOTAL
  ,cntSHEETSCOLOR as cntSHEETSCOLOR
  FROM FOREACH @TABLE IN @TABLELIST
  WHERE cntDATE BETWEEN @DateTimeStart AND @DateTimeEnd

Would be nice to read smth's comment on this

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99009 More actions · Answer 1

Do all the linked servers definitely have the table dbo.qc in the database w? Are all these objects exactly the same definition? Do you have have to use UNION (which is a very expensive operator) rather than UNION ALL?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ratbak SSC Eights! Points: 939 More actions · Answer 2

No, SQL Server doesn't let you pass tables or columns as parameters. You could derive something like this using dynamic SQL.

But I might also we concerned about protentional blocking when unioning together forty linked servers. I'd be more inclined to write each of the inserts into a temp or staging table independently, and then use group by or select distinct to eliminate duplicates (I assume that's the reason you used UNION instead of UNION ALL)

nkat SSChasing Mays Points: 654 More actions · Answer 3

Thank you, ratbak! Sorry, but I can't really catch your drift. Do you mean that with dynamic SQL it is somehow possible to do FOREACH or write less code?

nkat SSChasing Mays Points: 654 More actions · Answer 4

Hello!

Thank you, Thom for your questions!

Do all the linked servers definitely have the table dbo.qc in the database w?

Are all these objects exactly the same definition?

The answer is yes to both questions. I learned a minute ago what UNION ALL is. Will use this operator rather than UNION

With these answers in mind, do you think that there's a way to simplify UNION ALL query for 40 linked servers?

This reply was modified 2 years, 2 months ago by nkat.

ratbak SSC Eights! Points: 939 More actions · Answer 5

Do you mean that with dynamic SQL it is somehow possible to do FOREACH or write less code?

Dynamic SQL could achieve the "writing less code" goal using something like this:

SET NOCOUNT ON;

DECLARE @tablelist NVARCHAR(MAX) = N'[s00-0001-be01\w],[s00-0002-be01\w],[s00-0040\w]'

DROP TABLE IF EXISTS #servers
CREATE TABLE #servers
    (ServerName SYSNAME NOT NULL PRIMARY KEY);

INSERT INTO #servers (ServerName)
SELECT value
FROM STRING_SPLIT(@tablelist,',') tableList

--SELECT * FROM #servers;
    
DECLARE @sql NVARCHAR(max) = N'';
SELECT @sql = @sql + CONCAT('SELECT cntQUEUE as cntQUEUE,cntDate as cntDate,cntSHEETSTOTAL as cntSHEETSTOTAL,cntSHEETSCOLOR as cntSHEETSCOLOR
FROM ',#servers.ServerName,'.[w].[dbo].[qc]
WHERE cntDATE BETWEEN @DateTimeStart AND @DateTimeEnd UNION ALL 
')
FROM #servers;

SET @sql = LEFT(@sql,LEN(@sql) - 12)
PRINT @sql;

You could modify to insert into your chosen temp or staging table.

But unless the linked servers change/increase frequently, and given that the "more code" approach could be accomplished mostly w/ copy & paste (replacing linked server name for each), I wouldn't necessarily be too concerned about writing more code once.

In fact, if the list is relatively static, you could paste the code generated w/ dynamic sql into a script or stored procedure.

nkat SSChasing Mays Points: 654 More actions · Answer 6

ratbak wrote:

Dynamic SQL could achieve the "writing less code" goal using something like this:

Thank you, ratbak for the idea! I will learn from it

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 7

If possible, avoid linked servers.

your code may block others ( local to the linked server ) !

Maybe your process is even better off doing its stuff using e.g. SSIS ( or even direct powerbi of that is your end platform )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Michael L John One Orange Chip Points: 26551 More actions · Answer 8

Of far more interest to me is the reasoning behind having 40 linked servers, and apparently each has the same database structure.

What kinds of things were you trying to accomplish with this architecture?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/