How to Upsert Records in SQL(Sink) through ADF?

Introduction

We are performing Integration of Accounts from CRM to SQL using ADF Copy activity pipeline. We want to upsert the accounts instead of inserting duplicate records again.

Step 1:

Auto create the Table named “accounts” in SQL Server during the first Integration run by selecting the Auto create table option.

 

Step 2:

Create a custom data type named “AccountType” using following query.

 

CREATE TYPE AccountType AS TABLE(

accountid uniqueidentifier,

transactioncurrencyid uniqueidentifier,

address1_city nvarchar(MAX),

createdon datetime2(7),

accountnumber nvarchar(MAX),

name nvarchar(MAX),

address1_country nvarchar(MAX),

address1_composite nvarchar(MAX),

telephone1 nvarchar(MAX),

emailaddress1 nvarchar(MAX),

websiteurl nvarchar(MAX),

primarycontactid uniqueidentifier

)

Step 3:

Create a Stored Procedure named “spUpsertAccounts”.

 

CREATE PROCEDURE spUpsertAccounts

@account AccountType READONLY

AS

BEGIN

 

MERGE dbo.accounts AS target_sqldb

USING @account AS source_tblstg

ON (target_sqldb.accountid = source_tblstg.accountid)

WHEN MATCHED THEN

UPDATE SET

accountid = source_tblstg.accountid,

transactioncurrencyid = source_tblstg.transactioncurrencyid,

address1_city = source_tblstg.address1_city,

createdon = source_tblstg.createdon,

accountnumber = source_tblstg.accountnumber,

name = source_tblstg.name,

address1_country = source_tblstg.address1_country,

address1_composite = source_tblstg.address1_composite,

telephone1 = source_tblstg.telephone1,

emailaddress1 = source_tblstg.emailaddress1,

websiteurl = source_tblstg.websiteurl,

primarycontactid = source_tblstg.primarycontactid

 

 

WHEN NOT MATCHED THEN

INSERT (

accountid,

transactioncurrencyid,

address1_city,

createdon,

accountnumber,

name,

address1_country,

address1_composite,

telephone1,

emailaddress1,

websiteurl,

primarycontactid

)

VALUES (

source_tblstg.accountid,

source_tblstg.transactioncurrencyid,

source_tblstg.address1_city,

source_tblstg.createdon,

source_tblstg.accountnumber,

source_tblstg.name,

source_tblstg.address1_country,

source_tblstg.address1_composite,

source_tblstg.telephone1,

source_tblstg.emailaddress1,

source_tblstg.websiteurl,

source_tblstg.primarycontactid

);

END

Step 4:

Enter the Stored Procedure Name, Table Type and Table type parameter as shown in the image below:

 

 

Step 5:

Publish all the changes and debug your Pipeline.  You can Verify the results in SQL Server “accounts” table.

 


Share Story :