?
Solved

SQL Merige returns error code when updating

Posted on 2016-09-19
15
Medium Priority
?
84 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 66

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 66

Expert Comment

by:Jim Horn
ID: 41805442
Uhh .. no.  Use the CODE button to paste code into a question.
ask-question-buttons.jpg
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 41

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 41

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 41

Accepted Solution

by:
Sharath earned 1000 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 53

Assisted Solution

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

Author Comment

by:rwheeler23
ID: 41840153
ITEMNMBR
0
 
LVL 53

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 38

Assisted Solution

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

569 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