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,TO
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,ITMSH
UOFM,TOQTY,FROMQTY,UOMPRIC
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.PR
--When records are matched, update
--the records if there is any change
WHEN MATCHED
THEN
UPDATE SET TARGET.ITEMDESC = SOURCE.ITEMDESC,TARGET.ITM
TARGET.CURRCOST=SOURCE.CUR
TARGET.PRCLEVEL1=SOURCE.PR
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT ([ITEMNMBR],[ITEMDESC],[IT
,[TOQTY],[FROMQTY],[UOMPRI
VALUES
(ITEMNMBR,ITEMDESC,ITMSHNA
ISNULL(UOMPRICE,0.00));
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO
ITEMNMBR,PRCLEVEL8,UOFM,TO
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,ITMSH
UOFM,TOQTY,FROMQTY,UOMPRIC
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.PR
--When records are matched, update
--the records if there is any change
WHEN MATCHED
THEN
UPDATE SET TARGET.ITEMDESC = SOURCE.ITEMDESC,TARGET.ITM
TARGET.CURRCOST=SOURCE.CUR
TARGET.PRCLEVEL1=SOURCE.PR
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT ([ITEMNMBR],[ITEMDESC],[IT
,[TOQTY],[FROMQTY],[UOMPRI
VALUES
(ITEMNMBR,ITEMDESC,ITMSHNA
ISNULL(UOMPRICE,0.00));
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO
Jim Horn
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.
ASKER
I have put this code into a file. Is this what you mean?
rbs_ITEMCOMP.sql
rbs_ITEMCOMP.sql
Uhh .. no. Use the CODE button to paste code into a question.
ASKER
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,ITMSH
UOFM,TOQTY,FROMQTY,UOMPRIC
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.PR
--When records are matched, update
--the records if there is any change
WHEN MATCHED
THEN
UPDATE SET TARGET.ITEMDESC = SOURCE.ITEMDESC,TARGET.ITM
TARGET.CURRCOST=SOURCE.CUR
TARGET.PRCLEVEL1=SOURCE.PR
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT ([ITEMNMBR],[ITEMDESC],[IT
,[TOQTY],[FROMQTY],[UOMPRI
VALUES
(ITEMNMBR,ITEMDESC,ITMSHNA
ISNULL(UOMPRICE,0.00));
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO
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,ITMSH
UOFM,TOQTY,FROMQTY,UOMPRIC
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.PR
--When records are matched, update
--the records if there is any change
WHEN MATCHED
THEN
UPDATE SET TARGET.ITEMDESC = SOURCE.ITEMDESC,TARGET.ITM
TARGET.CURRCOST=SOURCE.CUR
TARGET.PRCLEVEL1=SOURCE.PR
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT ([ITEMNMBR],[ITEMDESC],[IT
,[TOQTY],[FROMQTY],[UOMPRI
VALUES
(ITEMNMBR,ITEMDESC,ITMSHNA
ISNULL(UOMPRICE,0.00));
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO
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(*) > 1ASKER
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.
Just wanted to check other possibilities. Do you have any triggers on the target table? What exactly the error message?
ASKER
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
T1.ITEMNMBR would never be NULL so I changed it to T3.ITEMNMBR but I still get the same PK violation message.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ITEMNMBR
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)>1SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.