CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresmerge
the_lone_note
the_lone_note

Reputation: 161

SQL Server : using MERGE statement to update two tables

I am trying to use a MERGE statement to update 2 tables using a single source (CTE). It works when I use only 1 merge, but when I add the 2nd one, it returns an error:

Msg 208, Level 16, State 1, Procedure mn_SeoUrl_UpdateBulk, Line 46
Invalid object name 'cte'.

Is it possible to update 2 tables with a merge? If it is possible, I am doing it incorrectly and I hope someone can show me what is the correct method of doing this.

Any help is much appreciated.

Thank you.

Here is my failing code (false col names):

WITH cte AS 
( 
    SELECT
        [u].[col1], [u].[col2], [u].[col3], 
        CASE                                        
           WHEN [u].[col1] LIKE 'L%' 
              THEN 'c/' + [u].[col2] + '/' + [u].[col3]
           WHEN [u].[col1] LIKE 'M%' 
              THEN 'm/' + [u].[col2] + '/' + [u].[col3]
        END [col4]
    FROM
        (SELECT
             [st1].[col1], [st1].[col2], [st1].[col3]
         FROM
             [dbo].[sourcetable1] [st1]
         INNER JOIN 
             [dbo].[sourcetable2] [st2] ON [st1].[ID] = [st2].[ID]
         WHERE
             [pd].[col2] <> 0) [u]
)
MERGE [dbo].[table1] AS [Target]
USING [cte] AS [Source] ON [Target].[ID] = [Source].[ID]

WHEN MATCHED THEN
    UPDATE 
        SET [Target].[col] = [Source].[col]

WHEN NOT MATCHED BY TARGET THEN
    INSERT ([col])
    VALUES ([Source].[col]);

MERGE [dbo].[tabl2] AS [Target]
USING [cte] AS [Source] ON [Target].[id] = [Source].[id]

WHEN MATCHED THEN
    UPDATE 
        SET [Target].[col] = [Source].[col]

WHEN NOT MATCHED BY TARGET THEN
    INSERT ([col])
    VALUES ([Source].[col]);
END;

Upvotes: 2

Views: 6664

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

Is it possible to update 2 tables with a merge?

No, it is not possible to update two tables with a single merge. You have to do two separate merge statements.

And as others have pointed out in comments, a single CTE can only be used for one statement, so if you do two merge statements, they can't share the same CTE. My suggestion would be to use your CTE query to populate a table variable. Then you can use the same table variable in two merge statements.

Upvotes: 6

Related Questions