I have this source table for my merge and the target table has the same fields.
ID Name Birth Work
123 john doe 01/20/2000 plumber
123 john doe 01/20/2000 carpenter
234 john ere 12/12/1990 driver
345 john wel 02/21/1998 janitor
but when I try to use Merge statement to transfer my data from source, it would prompt an error like "Duplicate primary key" and sometimes "Cannot update data since it has been updated once." How can I avoid these errors?
What I want to be done is, in my source table, it acts as an admission of workers(history) and my target data is the Master list. Can anyone help me how to avoid this errors?
This is my query:
MERGE EMPMASTER as T
USING (Select ID,Name,Birth,Work from ADMISSION) as S
ON (CAST(T.IDNum as int)=Cast(S.ID as int))
WHEN NOT MATCHED by TARGET
THEN
INSERT (IDNum,EMPName,EMPBirth,EMPWork)
Values (ID,Name,Birth,Work)
WHEN MATCHED THEN
UPDATE Set IDNum=ID,
EMPName=Name,
EMPBirth=Birth,
EMPWork=Work);
So that in my Master list table it would look like:
ID Name Birth Work
123 john doe 01/20/2000 carpenter
234 john ere 12/12/1990 driver
345 john wel 02/21/1998 janitor
You have
duplicates
on your source (based on the ID, which is your MERGE ON column).
You could either use grouping in your source so that it only takes one value per each ID, or you could cleanup the source beforehand to have only one record per ID.