Handling duplicate key insertion for a column with unique index exception

Question

I am trying to create a SQL stored procedure which is scheduled to run every day which is responsible for inserting data into a SQL table Table B from Table A where there is a unique index column named Change_ID in Table B. But there are some cases when the select query returns Change_IDs from source table Table A which are already present in the destination table Table B in which case the insertion fails as the procedure throws Cannot insert duplicate key row in object 'dbo.TableB' with unique index and job is aborted.

In the above case, I am trying to catch the exception and perform a delete on Table B to remove the duplicate and insert the row from Table A.

I have the following query and May I know a better way of handling this scenario.

    BEGIN TRY
      insert into TableB
        (   change_id
          , item_id    
          , class
          , subclass      
          , start_date
          , end_date

        )       
      select 
            change_id
          , item_id    
          , class
          , subclass      
          , start_date
          , end_date
      from openquery(ORA,'select * from TableA where start_date >= next_day(trunc(sysdate)-5,''MONDAY'')')
END TRY
BEGIN CATCH
--
END CATCH

Add a `where not exists` in B select Key from Table B to the tableA query; then you wouldn't ever hit the duplicate key error. — xQbert, May 31 '16 at 16:08
See also : http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update — PaulF, May 31 '16 at 16:12
@xQbert Well, as a matter of performance, I thought it would be better not to check for duplicates for each and every column. instead I thought handling exception gives a better performance NOT SURE though — DoIt, May 31 '16 at 16:13
@xQbert and also I do not want to skip any rows from Table A instead, I want to replace the ones in Table B with Table A if there is a duplicate — DoIt, May 31 '16 at 16:19
I no longer understand the question. You don't want duplicates, but if you do have them you want to replace the existing data with the new data. Sounds to me like you want to use [MERGE](https://msdn.microsoft.com/en-us/library/bb510625.aspx) (insert if not exists and update when key exists, delete when no longer exists?) But if all that's true... why not just delete all and then reinsert distinct? questions and data is too vague to help — xQbert, May 31 '16 at 17:02
@xQbert Sorry for the confusion but Table A is the source table where I am getting the data from and Table B is the destination table into which I am trying to insert the data. all I wanted to do is if a duplicate id is occurred then I would like to delete that record from table B and insert it from Table A — DoIt, May 31 '16 at 17:18
What about if the record was in B but isn't in the A data set? If the record should be removed from B since it's no longer in A, `Merge` it does everything you're asking for without having to write individual insert, update and delete statements..... If you dont want to use the merge, or want to keep records in B not in A anymore, then you must run two statements: delete from B when exists in A, then an insert from A to b. This will Leave records in B no longer in A. — xQbert, May 31 '16 at 17:20
@xQbert If the record was in B but not in A then I would like to continue insertion of that row into A. — DoIt, May 31 '16 at 17:26
So the resulting table B should have all entries that ever occurred in A, and B should only reflect the most recent A if a duplicate ever existed? (in other words B is everything that ever happened in A keeping on the most recent change for a change_ID? If b as entries 0,1,2,3 and A has 3,4,5 then you would have 0,1,2,3,4,5 but 3 would only be the values presently in A? If so then you must run two statements: delete from B when exists in A, then an two statements: Delete from B where in A, then insert a into B. With a catch, your addressing the symptom not the the problem. — xQbert, May 31 '16 at 17:35

score 0 · Answer 1 · answered May 31 '16 at 16:18

0

you can try it with on duplicate after your insert. Maybe this serves for you ;)

MySql on duplicate

answered May 31 '16 at 16:18

Caius

193
12

1

I do not want to update anything on duplicate, just want to delete and insert new row and I would like to do this only when there is a duplicate key exception. Also I don't think I can use on duplicate in TSQL – DoIt May 31 '16 at 16:44

Handling duplicate key insertion for a column with unique index exception

1 Answers1