none
Fire triggers when rows are being inserted by sqlbulkcopy RRS feed

  • Question

  • I am using sqlbulkcopy to insert bulk data into the sql database. But sometimes I may get duplicate values at that time I have to update than inserting a new data. I wrote insert triggers to to this task. But while using sql bulk copy class, it triggers only once after inserting all the rows. so duplicate occurs. Anybody help me in this? I have to fire my insert trigger everytime the row is being inserted.

    akita
    Thursday, June 16, 2011 6:06 AM

Answers

  • Hi Akitha,
    In your situation, the trigger will handle all rows at once other than one by one.If FIRE_TRIGGERS is specified for a bulk-import operation, it executes any INSERT and INSTEAD OF triggers that are defined on the table for all rows inserted into the table. You could get more details form this online document .


    The workaround could be as follows:
    1.  If you are on SQL 2008, you could use the MERGE statement, which permits you to perform INSERT and UPDATE in a single statement or use one INSERT WHERE NOT EXISTS and an UPDAT to avoid duplicate values.
    2. Or you could firstly insert the bulk records into another table like staging table as Olaf suggested in the former post.  And then run query like "insert into 'table with trigger' select * from 'table'"

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Monday, July 11, 2011 1:22 AM
    Monday, June 20, 2011 4:02 AM

All replies

  • Hello akita,

    That's quite normal and by design; a trigger always fires ones per transaction, so you have to redesign your construct. You could use a INSTEAD OF trigger and filter inside the trigger the rows you want to insert. Other option is to use a staging table and insert the corrected data into the target table afterwards.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Thursday, June 16, 2011 6:20 AM
    Moderator
  • hello,

           Thats fine using instead of trigger thing. But while inserting rows by using sqlbulkcopy, after all the rows copied, the trigger fires. I want my trigger (instead of, for whatever) should be fired for each row while using sqlbulcopy class to write bulk data. Is there any work around for this?


    akita
    Thursday, June 16, 2011 6:40 AM
  • No, a trigger fires per transaction, not per row.
    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    • Marked as answer by akitha Thursday, June 16, 2011 7:14 AM
    • Unmarked as answer by akitha Thursday, June 16, 2011 7:14 AM
    Thursday, June 16, 2011 7:12 AM
    Moderator
  • Hi Akitha,
    In your situation, the trigger will handle all rows at once other than one by one.If FIRE_TRIGGERS is specified for a bulk-import operation, it executes any INSERT and INSTEAD OF triggers that are defined on the table for all rows inserted into the table. You could get more details form this online document .


    The workaround could be as follows:
    1.  If you are on SQL 2008, you could use the MERGE statement, which permits you to perform INSERT and UPDATE in a single statement or use one INSERT WHERE NOT EXISTS and an UPDAT to avoid duplicate values.
    2. Or you could firstly insert the bulk records into another table like staging table as Olaf suggested in the former post.  And then run query like "insert into 'table with trigger' select * from 'table'"

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Peja Tao Monday, July 11, 2011 1:22 AM
    Monday, June 20, 2011 4:02 AM
  • Hello Peja.

         I am using Sql 2005, So I can't use the MERGE statement. Can you tell me how to use staging table effectively in my case?

    Regards,

    Akita.


    akita
    Monday, June 20, 2011 6:18 AM
  • Hi Akita,

    You could add one more column called 'number' with int value in staging table. While the table data has been bulk copy into this staging table, column 'number' will be set incrementally like 1, 2,3,4,....to member of the rows inserted. And customized staging table’s trigger by using while loop to INSERT WHERE NOT EXISTS and UPDATE records in destination table.
    So after the bulk copied data being inserted in staging table, the customized trigger will be fired and do a loop comparison by the number column to avoid duplicate values while inserting and updating.

    Hope this helps.

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, June 20, 2011 7:47 AM