update and insert into Azure data warehouse using Azure data factory pipelines

Question

I'm trying to run an adf copy pipeline with and update and insert statements that is supposed to replace merge statement. basically a statement like:

UPDATE TARGET 
SET ProductName = SOURCE.ProductName, 
TARGET.Rate = SOURCE.Rate 
FROM  Products AS TARGET
INNER JOIN UpdatedProducts AS SOURCE 
ON TARGET.ProductID = SOURCE.ProductID
WHERE TARGET.ProductName <> SOURCE.ProductName 
OR TARGET.Rate <> SOURCE.Rate

INSERT Products (ProductID, ProductName, Rate) 
SELECT SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate
FROM UpdatedProducts AS SOURCE 
WHERE NOT EXISTS
(
SELECT 1
FROM Products 
WHERE ProductID = SOURCE.ProductID
)

If the target is an azure sql db I would use this way: https://www.taygan.co/blog/2018/04/20/upsert-to-azure-sql-db-with-azure-data-factory but if the target is an adw a stored procedure option doesn't exist! any suggestion? do I have to have a staging table first then I run the update and insert statements from stg_table to target_table? or maybe there is any possibility to do it directly from adf?

JEuvin JEuvin · Accepted Answer · 2019-10-23T03:23:30

If you can't use a stored procedure, my suggestion would be to create a second copy data transform. Run the pre-script on the second transform and drop the table since its a temp table that you created on the first.

BEGIN

MERGE Target AS target_sqldb
USING TempTable AS source_tblstg
ON (target_sqldb.Id= source_tblstg.Id)

WHEN MATCHED THEN
    UPDATE SET
    [Name] = source_tblstg.Name,
    [State] = source_tblstg.State

WHEN NOT MATCHED THEN
    INSERT([Name], [State])
    VALUES (source_tblstg.Name, source_tblstg.State);

DROP TABLE TempTable;

END

update and insert into Azure data warehouse using Azure data factory pipelines

1 Answers