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.