MSSQL stored proc Duplicate Key Error handling

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

pascalbergeronAsked:
Who is Participating?
 
aaronakinConnect With a Mentor Commented:
Not really, but you do this...

INSERT INTO A
  SELECT *
    FROM B
    WHERE PKColumnName NOT IN (SELECT PKColumnName FROM A)
0
 
BrandonGalderisiCommented:
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
 
BrandonGalderisiConnect With a Mentor Commented:
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
 
aaronakinCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.