CODEWITHSUNDEEP
sqlsql-server-2008
user3312649
user3312649

Reputation: 300

SQL SERVER MERGE error

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

Upvotes: 0

Views: 55

Answers (2)

Faitus Joseph
Faitus Joseph

Reputation: 59

You are joining the Source and the Target tables on

(CAST(T.IDNum as int)=Cast(S.ID as int))

In your Source data sample, there are more than 1 row with same ID (123).

Since your source and the target tables have same fields, you can join with 'Work' field as well, in order to filter unique records in join combination. Below is the 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))
and (CAST(T.EMPWork as varchar)=Cast(S.Work as varchar))
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);

Upvotes: 0

Rigerta
Rigerta

Reputation: 4039

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.

Upvotes: 2

Related Questions