• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

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

0
pascalbergeron
Asked:
pascalbergeron
  • 2
  • 2
2 Solutions
 
aaronakinCommented:
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
 
BrandonGalderisiCommented:
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now