Solved

MSSQL stored proc Duplicate Key Error handling

Posted on 2008-10-01
4
286 Views
Last Modified: 2013-11-15
Hi.
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

0
Comment
Question by:pascalbergeron
  • 2
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
aaronakin earned 250 total points
Comment Utility
Not really, but you do this...

INSERT INTO A
  SELECT *
    FROM B
    WHERE PKColumnName NOT IN (SELECT PKColumnName FROM A)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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

0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 250 total points
Comment Utility
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
0
 
LVL 11

Expert Comment

by:aaronakin
Comment Utility
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

771 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

10 Experts available now in Live!

Get 1:1 Help Now