Solved

SQL Merige returns error code when updating

Posted on 2016-09-19
15
46 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 45

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 45

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 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now