0

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.

7
  • 5
    Insert all linked server data into temp tables, then run your query on the temp tables. Commented Jul 6, 2023 at 18:44
  • 5
    SELECT * FROM (WITH... isn't valid syntax. You define your CTEs at the start of the statement not in a derived table. Commented Jul 6, 2023 at 18:53
  • @ThomA, it might be valid query in the linked server. i think mysql supports this weird WITH construct for example Commented Jul 6, 2023 at 21:14
  • can't you do this as a UNION ALL of OPENQUERY calls instead of EXEC AT Server? Commented Jul 6, 2023 at 21:16
  • @siggemannen does MySQL support OPENQUERY? I thought that was SQL Server-specific? Commented Jul 6, 2023 at 23:35

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.