Skip to main content

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 OS_Material_Data] AS tgt
USING (
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_Material
WHEN 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_DescriptionUp

WHEN 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,

 

Hi @Jackolas,

The “A MERGE statement must be terminated by a semi-colon (;)” error message you are seeing when trying to do a transformation in CData Sync is caused by a known issue which has been addressed by our developers not too long ago. From the looks of it, you might be using an impacted version and the next best course of action is to upgrade to the latest version of CData Sync in case you have not done so already. I would encourage you to download and run a test with the website build as we have just recently rolled out a new update: https://www.cdata.com/sync/builds/

If you have any other questions feel free to reach out to the CData support team at [email protected] and one of our sync support specialists will be happy to assist you.


Updated CData Sync to the latest version (24.2.9064.0) and the problem is indeed fixed.

Many thanks.