locked
Merge with table value parameter fails on a PK violation, how do you get which row failed? RRS feed

  • Question

  • I have a stored procedure, which takes 2 table value parameters.  I got some good help on a previous question ( here ) that let me merge my datatables into the existing.  Unfortunately, some of my data coming in could contain duplicated data that is already in the tables and violates a key constraint.  If i am inserting several hundred or 1000 rows, how can i determine which of the rows is actually failing the constraint?  I am calling the stored procedure from a c# application and getting the SQL Exception returned, but I see no way to determine which one(s) are already in the table.

    I would ideally like to throw out the violating rows and have the remainder commit without having to do further queries on the application side.  There must be some way to do this within the stored procedure.


    • Edited by DBchBm Tuesday, January 22, 2013 4:44 AM Clarity
    Tuesday, January 22, 2013 4:42 AM

Answers

  • I found my logical flaw that was hanging me up.  Thanks for the response.
    • Marked as answer by Naomi N Wednesday, January 23, 2013 3:47 AM
    Tuesday, January 22, 2013 6:40 AM

All replies

  • Just to see if the current insert value is an existing PK column value or not in the table. The answer should be yes and it is why you've got violation.

    I suggest you have a pre-check for this in your codes.


    Many Thanks & Best Regards, Hua Min



    Tuesday, January 22, 2013 4:52 AM
  • You can capture the value which is throwing error by having a try catch loop. Below is an example to find out which value is throwing error.

    declare @temp table
    (id varchar(100))
    
    declare @targettemp table
    (id int)
    
    insert into @temp select '1'
    insert into @temp select '2'
    insert into @temp select '3'
    insert into @temp select 'a'
    insert into @temp select '4'
    
    declare @id varchar(100)
    
    select @id = MIN(id) from @temp
    while @id is not null 
    begin
    
    begin try
    insert into @targettemp select @id
    end try
    begin catch
    	select @id
    end catch
    
    
    
    delete from @temp where id = @id
    select @id = MIN(id) from @temp
    end
    


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Tuesday, January 22, 2013 6:02 AM
  • I found my logical flaw that was hanging me up.  Thanks for the response.
    • Marked as answer by Naomi N Wednesday, January 23, 2013 3:47 AM
    Tuesday, January 22, 2013 6:40 AM