I’m trying to do a transformation after a dataload to a SQL server.
The query used is: (the query is tested and works on the SQL server)
MERGE INTO [S_Material_Data] AS tgtUSING ( SELECT HASHBYTES('SHA2_256', mat.MatNr) AS HSH_Material, CR_date AS Creation_Date, CASE WHEN mat.CH_Date IS NOT NULL THEN mat.CH_Date ELSE mat.CR_date END AS Changed_On, mat.Mat_Type AS Material_Type, mat.Mat_Grp AS Material_Group, mat.PRD_Hier AS Product_Hierarchy, Mat_Disc AS Material_Description, Mat_DiscU AS Material_DescriptionUp FROM Materials mat LEFT OUTER JOIN Descriptions dsc ON mat.MatNr = dsc.MatNr AND dsc.Lang = 'E' ) AS src ON tgt.HSH_Material = src.HSH_MaterialWHEN MATCHED THEN UPDATE SET tgt.Creation_Date = src.Creation_Date, tgt.Changed_On = src.Changed_On, tgt.Material_Type = src.Material_Type, tgt.Material_Group = src.Material_Group, tgt.Product_Hierarchy = src.Product_Hierarchy, tgt.Material_Description = src.Material_Description, tgt.Material_DescriptionUp = src.Material_DescriptionUpWHEN NOT MATCHED BY TARGET THEN INSERT (HSH_Material, Creation_Date, Changed_On, Material_Type, Material_Group, Product_Hierarchy, Material_Description, Material_DescriptionUp) VALUES (src.HSH_Material, src.Creation_Date, src.Changed_On, src.Material_Type, src.Material_Group, src.Product_Hierarchy, src.Material_Description, src.Material_DescriptionUp);
but when i try to save this query the last semicolon (
is removed
and then when i try to run the query i get the following error:
A MERGE statement must be terminated by a semi-colon (;).
Is there a way to work around this issue and to make sure that the semicolon ( is added to the query?
many thanks,