DELETE case is not being considered in the temporary table
Description
There is an INSERT statement pattern that requires a specific transformation, which involves the creation of a temporary table. This FDM notifies that the DELETE case is not considered in the transformation mentioned. Please visit INSERT with Table DML Factor with MERGE as DML to get more information about this pattern.
Input Code:
IN -> SqlServer_01.sql
INSERT INTO T3
SELECT
col1,
col2
FROM (
MERGE T1 USING T2
ON T1.col1 = T2.col1
WHEN NOT MATCHED THEN
INSERT VALUES ( T2.col1, T2.col2 )
WHEN MATCHED THEN
UPDATE SET T1.col2 = t2.col2
OUTPUT
$action ACTION_OUT,
T2.col1,
T2.col2
) AS MERGE_OUT
WHERE ACTION_OUT='UPDATE';
Output Code:
OUT -> SqlServer_01.sql
--** SSC-FDM-TS0026 - DELETE CASE IS NOT BEING CONSIDERED, PLEASE CHECK IF THE ORIGINAL MERGE PERFORMS IT **
CREATE OR REPLACE TEMPORARY TABLE MERGE_OUT AS
SELECT
CASE
WHEN T1.$1 IS NULL
THEN 'INSERT'
ELSE 'UPDATE'
END ACTION_OUT,
T2.col1,
T2.col2
FROM
T2
LEFT JOIN
T1
ON T1.col1 = T2.col1;
MERGE INTO T1
USING T2
ON T1.col1 = T2.col1
WHEN NOT MATCHED THEN
INSERT VALUES (T2.col1, T2.col2)
WHEN MATCHED THEN
UPDATE SET
T1.col2 = t2.col2
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - OUTPUT CLAUSE NOT SUPPORTED IN SNOWFLAKE ***/!!!
OUTPUT
$action ACTION_OUT,
T2.col1,
T2.col2 ;
INSERT INTO T3
SELECT
col1,
col2
FROM
MERGE_OUT
WHERE
ACTION_OUT ='UPDATE';
