How to update the source table in the MERGE statement?

Question

I have a table tbBroker on my local database and a table Agency on linked server object "DIS".

I am trying to migrate data from local's tbBroker to DIS's Agency table

Also note that

tbBroker's Columns  =  Agency's Columns
BrokerCode          =  AgencyNumber
BusinesssName       =  AgencyName
City                =  City
tbSystemUser.EmailAddress = EmailAddress

Also, tbBroker has two additional columns DISImportFlag bit and DISCreatTS datetime. These two fields should be updated to 1 and GETDATE() whenever data is migrated (during insertion) from tbBroker to Agency table in order to determine which rows have been migrated.

I have written the following query

       USE [DISTemp];
       MERGE INTO
       [dbo].[Agency] AS [TARGET]
       USING
       [aginbr].[dbo].[tbBroker] AS [SOURCE]
       ON
       [TARGET].[AgencyNumber] COLLATE Latin1_General_CI_AI = [SOURCE].[BrokerCode]
       WHEN NOT MATCHED BY TARGET THEN
       INSERT (    
       [AgencyName]
       , [Address1]
       , [Address2]
       , [PostalCode]
       , [City]
       , [Phone]
       , [EmailAddress]  
       )
       VALUES (
       [SOURCE].[BUSINESSNAME]
       , [SOURCE].[ADDRESS]
       , [SOURCE].[AddressLine2]
       , [SOURCE].[Zip]
       , [SOURCE].[City]
       , [SOURCE].[Phone]
       , [SOURCE].[Email]
       )
       UPDATE SET [SOURCE].[DISImportFlag] = 1,[SOURCE].[DISCreatTS] = GETDATE()
       WHEN MATCHED THEN
       UPDATE SET
       [TARGET].[AgencyName] = [SOURCE].[BUSINESSNAME]
        , [TARGET].[Address1] = [SOURCE].[ADDRESS]
        , [TARGET].[Address2] = [SOURCE].[AddressLine2]
        , [TARGET].[PostalCode] = [SOURCE].[Zip]
        , [TARGET].[City] = [SOURCE].[City]
        , [TARGET].[Phone] = [SOURCE].[Phone]
        , [TARGET].[EmailAddress] = [SOURCE].[Email];

When we execute the above query it throws error message

A MERGE statement must be terminated by a semi-colon (;)

after adding the following line

   UPDATE SET [SOURCE].[DISImportFlag] = 1,[SOURCE].[DISCreatTS] = GETDATE()

---

Edit from comments

The source table tbBroker has a primary key column BrokerID.

I want to update in the source table those rows that were inserted into the target table, i.e. those rows that didn't exist in the target table before MERGE.

Answer 1

I don't think you can change some table other than TARGET using a single MERGE statement. But, you can use OUTPUT clause to capture the result of the MERGE in a helper/temporary table and then update your SOURCE based on that.

You said that you want to update only those rows that were inserted into the Target, i.e. those that didn't exist in the Target before MERGE.

Output IDs of inserted rows into the temporary table and then use it to update the Source table.

CREATE TABLE #Temp(
    BrokerID int NOT NULL,
CONSTRAINT [PK_Broker] PRIMARY KEY CLUSTERED
(
    BrokerID ASC
));

INSERT INTO #Temp (BrokerID)
SELECT TableChanges.BrokerID
FROM
    (
        MERGE INTO [dbo].[Agency] AS [TARGET]
        USING [aginbr].[dbo].[tbBroker] AS [SOURCE]
        ON [TARGET].[AgencyNumber] COLLATE Latin1_General_CI_AI = [SOURCE].[BrokerCode]
        WHEN NOT MATCHED BY TARGET THEN
        INSERT
        (
            [AgencyName]
            , [Address1]
            , [Address2]
            , [PostalCode]
            , [City]
            , [Phone]
            , [EmailAddress]
        )
        VALUES
        (
            [SOURCE].[BUSINESSNAME]
            , [SOURCE].[ADDRESS]
            , [SOURCE].[AddressLine2]
            , [SOURCE].[Zip]
            , [SOURCE].[City]
            , [SOURCE].[Phone]
            , [SOURCE].[Email]
        )
        WHEN MATCHED THEN
        UPDATE SET
            [TARGET].[AgencyName] = [SOURCE].[BUSINESSNAME]
            , [TARGET].[Address1] = [SOURCE].[ADDRESS]
            , [TARGET].[Address2] = [SOURCE].[AddressLine2]
            , [TARGET].[PostalCode] = [SOURCE].[Zip]
            , [TARGET].[City] = [SOURCE].[City]
            , [TARGET].[Phone] = [SOURCE].[Phone]
            , [TARGET].[EmailAddress] = [SOURCE].[Email]
        OUTPUT $action, [SOURCE].BrokerID
    ) AS TableChanges (MergeAction, BrokerID)
WHERE TableChanges.MergeAction = 'INSERT'
;

UPDATE [aginbr].[dbo].[tbBroker]
SET
    [DISImportFlag] = 1
    ,[DISCreatTS] = GETDATE()
WHERE
    BrokerID IN 
    (
        SELECT T.BrokerID FROM #Temp AS T
    )
;

DROP TABLE #Temp;

Obviously, wrap this whole thing in a transaction and TRY ... CATCH and add appropriate error handling.