In a stored procedure, I have a few With clauses, of a query executed at linked server A:
DECLARE @myQuery VARCHAR(MAX)
SET @myQuery = 'SELECT * FROM (WITH
A AS (Select ... from ...),
B AS (Select ... from ...),
C AS (Select ... from ...)
Select A., B., C.
From A
Join B On
Join C On
')
INSERT INTO Table_A
EXEC (@myQuery) AT Linked_Server_A
Now I want to Union All a query executed at linked server B, with same structure, in the With clause B. It shall look like:
DECLARE @myQuery VARCHAR(MAX)
SET @myQuery = 'SELECT * FROM (WITH
A AS (Select ... from ...),
B AS (Select ... from ...
UNION ALL
Select ... from Openquery(linked server B, 'Select ... from...')),
C AS (Select ... from ...)
Select A., B., C.
From A
Join B On
Join C On
')
INSERT INTO Table_A
EXEC (@myQuery) AT Linked_Server_A
I would like to know if there is a simple way to achieve this. Appreciate for your feedback.
SELECT * FROM (WITH...isn't valid syntax. You define your CTEs at the start of the statement not in a derived table.