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
.
Best Answer
I don't think you can change some table other than
TARGET
using a singleMERGE
statement. But, you can useOUTPUT
clause to capture the result of theMERGE
in a helper/temporary table and then update yourSOURCE
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 theSource
table.Obviously, wrap this whole thing in a transaction and
TRY ... CATCH
and add appropriate error handling.