Solved

SQL Merige returns error code when updating

Posted on 2016-09-19
15
52 Views
Last Modified: 2016-10-26
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
0
Comment
Question by:rwheeler23
  • 7
  • 3
  • 2
  • +2
15 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41805407
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
 

Author Comment

by:rwheeler23
ID: 41805439
I have put this code into a file. Is this what you mean?
rbs_ITEMCOMP.sql
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41805442
Uhh .. no.  Use the CODE button to paste code into a question.
ask-question-buttons.jpg
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:rwheeler23
ID: 41805535
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
 
LVL 40

Expert Comment

by:Sharath
ID: 41805723
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
 

Author Comment

by:rwheeler23
ID: 41805794
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
 
LVL 40

Expert Comment

by:Sharath
ID: 41805804
Just wanted to check other possibilities. Do you have any triggers on the target table? What exactly the error message?
0
 

Author Comment

by:rwheeler23
ID: 41805817
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
 

Author Comment

by:rwheeler23
ID: 41805818
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
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 41805822
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
 

Author Comment

by:rwheeler23
ID: 41805847
T1.ITEMNMBR would never be NULL so I changed it to T3.ITEMNMBR but I still get the same PK violation message.
0
 
LVL 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 41839912
What is the primary key from rbs_ITEMCOMP table?
0
 

Author Comment

by:rwheeler23
ID: 41840153
ITEMNMBR
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41840165
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
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 125 total points
ID: 41840588
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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question