SQL Merige returns error code when updating

When I run this after issuing a truncate table command it executes fine. However if I run it  a second time it returns error code 2627. How do I track down which record is causing the error? It is the update that fails but why? There is no violation of primary key. The PK is:
ITEMNMBR,PRCLEVEL8,UOFM,TOQTY,FROMQTY

BEGIN TRY
--Insert or update Additional Information text into internet information table
--Synchronize the target table with
--refreshed data from source table
MERGE rbs_ITEMCOMP AS TARGET
USING (SELECT T1.ITEMNMBR,ITEMDESC,ITMSHNAM,ITMGEDSC,ITMCLSCD,STNDCOST,CURRCOST,LISTPRCE,ITEMSHWT,UOMSCHDL,PRICEGROUP,T1.PRCLEVEL as PRCLEVEL1,SELNGUOM,T3.PRCLEVEL as PRCLEVEL8,
UOFM,TOQTY,FROMQTY,UOMPRICE
FROM IV00101 T1
LEFT OUTER JOIN IV00105 T2 ON T1.ITEMNMBR=T2.ITEMNMBR
LEFT OUTER JOIN IV00108 T3 ON T1.ITEMNMBR=T3.ITEMNMBR) AS SOURCE
ON (TARGET.ITEMNMBR = SOURCE.ITEMNMBR and TARGET.PRCLEVEL8=SOURCE.PRCLEVEL8 and TARGET.UOFM = SOURCE.UOFM and TARGET.TOQTY = SOURCE.TOQTY and TARGET.FROMQTY = SOURCE.FROMQTY)
--When records are matched, update
--the records if there is any change
WHEN MATCHED
THEN
UPDATE SET TARGET.ITEMDESC = SOURCE.ITEMDESC,TARGET.ITMSHNAM=SOURCE.ITMSHNAM,TARGET.ITMGEDSC=SOURCE.ITMGEDSC,TARGET.ITMCLSCD=SOURCE.ITMCLSCD,TARGET.STNDCOST=SOURCE.STNDCOST,
TARGET.CURRCOST=SOURCE.CURRCOST,TARGET.LISTPRCE=SOURCE.LISTPRCE,TARGET.ITEMSHWT=SOURCE.ITEMSHWT,TARGET.UOMSCHDL=SOURCE.UOMSCHDL,TARGET.PRICEGROUP=SOURCE.PRICEGROUP,
TARGET.PRCLEVEL1=SOURCE.PRCLEVEL1,TARGET.SELNGUOM=SOURCE.SELNGUOM,TARGET.UOMPRICE =SOURCE.UOMPRICE

--When no records are matched, insert the incoming records from source table to target table

WHEN NOT MATCHED BY TARGET THEN
INSERT ([ITEMNMBR],[ITEMDESC],[ITMSHNAM],[ITMGEDSC],[ITMCLSCD],[STNDCOST],[CURRCOST],[LISTPRCE],[ITEMSHWT],[UOMSCHDL],[PRICEGROUP],[PRCLEVEL1],[SELNGUOM],[PRCLEVEL8],[UOFM]
           ,[TOQTY],[FROMQTY],[UOMPRICE])
                VALUES
           (ITEMNMBR,ITEMDESC,ITMSHNAM,ITMGEDSC,ITMCLSCD,STNDCOST,CURRCOST,LISTPRCE,ITEMSHWT,UOMSCHDL,PRICEGROUP,PRCLEVEL1,SELNGUOM,ISNULL(PRCLEVEL8,''),ISNULL(UOFM,''),ISNULL(TOQTY,1),ISNULL(FROMQTY,999999999999.99000),
               ISNULL(UOMPRICE,0.00));

END TRY

BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO
rwheeler23Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
You can exclude NULL possible records by adding a filter condition.
BEGIN TRY
 --Insert or update Additional Information text into internet information table
 --Synchronize the target table with
 --refreshed data from source table
 MERGE rbs_ITEMCOMP AS TARGET
 USING (SELECT T1.ITEMNMBR,ITEMDESC,ITMSHNAM,ITMGEDSC,ITMCLSCD,STNDCOST,CURRCOST,LISTPRCE,ITEMSHWT,UOMSCHDL,PRICEGROUP,T1.PRCLEVEL as PRCLEVEL1,SELNGUOM,T3.PRCLEVEL as PRCLEVEL8,
 UOFM,TOQTY,FROMQTY,UOMPRICE
 FROM IV00101 T1
 LEFT OUTER JOIN IV00105 T2 ON T1.ITEMNMBR=T2.ITEMNMBR
 LEFT OUTER JOIN IV00108 T3 ON T1.ITEMNMBR=T3.ITEMNMBR
 WHERE T1.ITEMNMBR IS NOT NULL) AS SOURCE 
 ON (TARGET.ITEMNMBR = SOURCE.ITEMNMBR and TARGET.PRCLEVEL8=SOURCE.PRCLEVEL8 and TARGET.UOFM = SOURCE.UOFM and TARGET.TOQTY = SOURCE.TOQTY and TARGET.FROMQTY = SOURCE.FROMQTY) 
 --When records are matched, update 
 --the records if there is any change
 WHEN MATCHED 
 THEN 
 UPDATE SET TARGET.ITEMDESC = SOURCE.ITEMDESC,TARGET.ITMSHNAM=SOURCE.ITMSHNAM,TARGET.ITMGEDSC=SOURCE.ITMGEDSC,TARGET.ITMCLSCD=SOURCE.ITMCLSCD,TARGET.STNDCOST=SOURCE.STNDCOST,
 TARGET.CURRCOST=SOURCE.CURRCOST,TARGET.LISTPRCE=SOURCE.LISTPRCE,TARGET.ITEMSHWT=SOURCE.ITEMSHWT,TARGET.UOMSCHDL=SOURCE.UOMSCHDL,TARGET.PRICEGROUP=SOURCE.PRICEGROUP,
 TARGET.PRCLEVEL1=SOURCE.PRCLEVEL1,TARGET.SELNGUOM=SOURCE.SELNGUOM,TARGET.UOMPRICE =SOURCE.UOMPRICE

 --When no records are matched, insert the incoming records from source table to target table

 WHEN NOT MATCHED BY TARGET THEN 
 INSERT ([ITEMNMBR],[ITEMDESC],[ITMSHNAM],[ITMGEDSC],[ITMCLSCD],[STNDCOST],[CURRCOST],[LISTPRCE],[ITEMSHWT],[UOMSCHDL],[PRICEGROUP],[PRCLEVEL1],[SELNGUOM],[PRCLEVEL8],[UOFM]
            ,[TOQTY],[FROMQTY],[UOMPRICE])
                 VALUES
            (ITEMNMBR,ITEMDESC,ITMSHNAM,ITMGEDSC,ITMCLSCD,STNDCOST,CURRCOST,LISTPRCE,ITEMSHWT,UOMSCHDL,PRICEGROUP,PRCLEVEL1,SELNGUOM,ISNULL(PRCLEVEL8,''),ISNULL(UOFM,''),ISNULL(TOQTY,1),ISNULL(FROMQTY,999999999999.99000),
                ISNULL(UOMPRICE,0.00));

 END TRY

 BEGIN CATCH
     SELECT ERROR_NUMBER() AS ErrorNumber;
 END CATCH;
 GO

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
It would greatly help if you can put the above code in a code block, and indent with a couple of spaces (as the EE editor interprets tabs as 8-10 spaces), to make it much easier to read.  Thanks in advance.
0
 
rwheeler23Author Commented:
I have put this code into a file. Is this what you mean?
rbs_ITEMCOMP.sql
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Uhh .. no.  Use the CODE button to paste code into a question.
ask-question-buttons.jpg
0
 
rwheeler23Author Commented:
Sorry about that, I hope this make the code more readable.

BEGIN TRY
 --Insert or update Additional Information text into internet information table
 --Synchronize the target table with
 --refreshed data from source table
 MERGE rbs_ITEMCOMP AS TARGET
 USING (SELECT T1.ITEMNMBR,ITEMDESC,ITMSHNAM,ITMGEDSC,ITMCLSCD,STNDCOST,CURRCOST,LISTPRCE,ITEMSHWT,UOMSCHDL,PRICEGROUP,T1.PRCLEVEL as PRCLEVEL1,SELNGUOM,T3.PRCLEVEL as PRCLEVEL8,
 UOFM,TOQTY,FROMQTY,UOMPRICE
 FROM IV00101 T1
 LEFT OUTER JOIN IV00105 T2 ON T1.ITEMNMBR=T2.ITEMNMBR
 LEFT OUTER JOIN IV00108 T3 ON T1.ITEMNMBR=T3.ITEMNMBR) AS SOURCE
 ON (TARGET.ITEMNMBR = SOURCE.ITEMNMBR and TARGET.PRCLEVEL8=SOURCE.PRCLEVEL8 and TARGET.UOFM = SOURCE.UOFM and TARGET.TOQTY = SOURCE.TOQTY and TARGET.FROMQTY = SOURCE.FROMQTY)
 --When records are matched, update
 --the records if there is any change
 WHEN MATCHED
 THEN
 UPDATE SET TARGET.ITEMDESC = SOURCE.ITEMDESC,TARGET.ITMSHNAM=SOURCE.ITMSHNAM,TARGET.ITMGEDSC=SOURCE.ITMGEDSC,TARGET.ITMCLSCD=SOURCE.ITMCLSCD,TARGET.STNDCOST=SOURCE.STNDCOST,
 TARGET.CURRCOST=SOURCE.CURRCOST,TARGET.LISTPRCE=SOURCE.LISTPRCE,TARGET.ITEMSHWT=SOURCE.ITEMSHWT,TARGET.UOMSCHDL=SOURCE.UOMSCHDL,TARGET.PRICEGROUP=SOURCE.PRICEGROUP,
 TARGET.PRCLEVEL1=SOURCE.PRCLEVEL1,TARGET.SELNGUOM=SOURCE.SELNGUOM,TARGET.UOMPRICE =SOURCE.UOMPRICE

 --When no records are matched, insert the incoming records from source table to target table

 WHEN NOT MATCHED BY TARGET THEN
 INSERT ([ITEMNMBR],[ITEMDESC],[ITMSHNAM],[ITMGEDSC],[ITMCLSCD],[STNDCOST],[CURRCOST],[LISTPRCE],[ITEMSHWT],[UOMSCHDL],[PRICEGROUP],[PRCLEVEL1],[SELNGUOM],[PRCLEVEL8],[UOFM]
            ,[TOQTY],[FROMQTY],[UOMPRICE])
                 VALUES
            (ITEMNMBR,ITEMDESC,ITMSHNAM,ITMGEDSC,ITMCLSCD,STNDCOST,CURRCOST,LISTPRCE,ITEMSHWT,UOMSCHDL,PRICEGROUP,PRCLEVEL1,SELNGUOM,ISNULL(PRCLEVEL8,''),ISNULL(UOFM,''),ISNULL(TOQTY,1),ISNULL(FROMQTY,999999999999.99000),
                ISNULL(UOMPRICE,0.00));

 END TRY

 BEGIN CATCH
     SELECT ERROR_NUMBER() AS ErrorNumber;
 END CATCH;
 GO

Open in new window

0
 
SharathData EngineerCommented:
Could you check if this query is returning duplicates?
select ITEMNMBR,PRCLEVEL8,UOFM,TOQTY,FROMQTY
  from (
SELECT T1.ITEMNMBR, ITEMDESC, ITMSHNAM, ITMGEDSC, ITMCLSCD,  STNDCOST, CURRCOST, LISTPRCE, ITEMSHWT, UOMSCHDL,PRICEGROUP, T1.PRCLEVEL as PRCLEVEL1,SELNGUOM,T3.PRCLEVEL as PRCLEVEL8,
UOFM,TOQTY,FROMQTY,UOMPRICE
FROM IV00101 T1
LEFT OUTER JOIN IV00105 T2 ON T1.ITEMNMBR=T2.ITEMNMBR
LEFT OUTER JOIN IV00108 T3 ON T1.ITEMNMBR=T3.ITEMNMBR) source
group by ITEMNMBR,PRCLEVEL8,UOFM,TOQTY,FROMQTY
having count(*) > 1

Open in new window

0
 
rwheeler23Author Commented:
No records are returned. Even if there were records then the update would kick in, correct? I do not see what is casing the duplicate record.
0
 
SharathData EngineerCommented:
Just wanted to check other possibilities. Do you have any triggers on the target table? What exactly the error message?
0
 
rwheeler23Author Commented:
I have found a clue. It is possible that there are no corresponding records in the IV00108 table so values for the primary key fields are NULL. I am trying to figure out how to account for that.
0
 
rwheeler23Author Commented:
Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint 'PK_rbs_ITEMCOMP'. Cannot insert duplicate key in object 'dbo.rbs_ITEMCOMP'. The duplicate key value is (108                            ,            ,          , 1.00000, 999999999999.99000).
The statement has been terminated.
0
 
rwheeler23Author Commented:
T1.ITEMNMBR would never be NULL so I changed it to T3.ITEMNMBR but I still get the same PK violation message.
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
What is the primary key from rbs_ITEMCOMP table?
0
 
rwheeler23Author Commented:
ITEMNMBR
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check if the following returns records:
SELECT T1.ITEMNMBR, COUNT(1) Repeated
FROM IV00101 T1
	 LEFT OUTER JOIN IV00105 T2 ON T1.ITEMNMBR=T2.ITEMNMBR
	 LEFT OUTER JOIN IV00108 T3 ON T1.ITEMNMBR=T3.ITEMNMB
GROUP BY T1.ITEMNMBR
WHERE COUNT(1)>1

Open in new window

0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Changed Where to Having for last comment.

SELECT T1.ITEMNMBR, COUNT(1) Repeated
FROM IV00101 T1
	 LEFT OUTER JOIN IV00105 T2 ON T1.ITEMNMBR=T2.ITEMNMBR
	 LEFT OUTER JOIN IV00108 T3 ON T1.ITEMNMBR=T3.ITEMNMB
GROUP BY T1.ITEMNMBR
HAVING COUNT(1)>1

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.