MSSQL stored proc Duplicate Key Error handling

Posted on 2008-10-01
Last Modified: 2013-11-15
Is it possible to know witch row is duplicated during a insert into
Here's my example:

-- MSSQL Stored Proc
Begin Try
   Insert into A (Select * From B)
End Try
Begin Catch
 -- Here i want to know witch row was not inserted because of a duplicated error
End Catch

Many thanks

Question by:pascalbergeron
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 11

Accepted Solution

aaronakin earned 250 total points
ID: 22615251
Not really, but you do this...

    FROM B
    WHERE PKColumnName NOT IN (SELECT PKColumnName FROM A)
LVL 39

Expert Comment

ID: 22615273
error_number() = 2627
Begin Try
   Insert into A (Select * From B)
End Try
Begin Catch
  if error_number() = 2627
    print 'Violation of %ls constraint '--'%.*ls'. Cannot insert duplicate key in object '%.*ls'.'
End Catch

Open in new window

LVL 39

Assisted Solution

BrandonGalderisi earned 250 total points
ID: 22615303
That's detecting it... to prevent it.

insert into A
select b.* from b
 left outer join a
  on b.WhatFieldLinks = a.WhatFieldLinks
where a.WhatFieldLinks is null
LVL 11

Expert Comment

ID: 22615379
BrandonGalderisi, I believe your first solution will catch the violation error, but it will not continue to insert the non-duplicate records.

pascalbergeron, you're best choice would be to use either query that BrandonGalderisi or I provided to make sure the duplicate rows do not get inserted.

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

717 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