Using Merge - SQL Data Warehouse

_{Wednesday, August 8, 2018 9:42 AM}

Hi Guys,

How can i use MERGE statement on  SQL Data Warehouse?  Tried using the merge statement for update and creation in my Data Warehouse but i keep on getting the following error message.

ErrorMessage:

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 1, column: 1: Incorrect syntax near 'MERGE'.

My script is as follows:

MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 
--When records are matched, update 
--the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName 
OR TARGET.Rate <> SOURCE.Rate THEN 
UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
TARGET.Rate = SOURCE.Rate 
--When no records are matched, insert
--the incoming records from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN 
INSERT (ProductID, ProductName, Rate) 
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate);

Regards,

Thabo

_{Wednesday, August 8, 2018 9:47 AM}

Hello,

See MERGE (Transact-SQL); not supported in SQL Datawarehouse.

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Wednesday, August 8, 2018 1:00 PM}

Hi,

Thanks for the reply. is there an alternative way i can use to do upsert into a Data Warehouse tables?

Regards,

Thabo

_{Wednesday, August 8, 2018 1:28 PM}

Hi,

Thanks for the reply. is there an alternative way i can use to do upsert into a Data Warehouse tables?

Regards,

Thabo

yes

use UPDATE/INSERT statements

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
)

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Wednesday, August 8, 2018 1:49 PM}

Azure SQL Data Warehouse is not standard SQL Server. 

I suggest you read this:

/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-ctas#replace-merge-statements

/en-us/azure/sql-data-warehouse/sql-data-warehouse-migrate-code