0

I am doing VB load and SSIS load. So that from these loads data is inserting into database tables. I need to find if there is any duplicate records are inserting into the tables. If any duplicate records are going into the table then i need to fire a trigger at the insertion of duplicate record and i need to insert the same record into another table.

For example y VB load or SSIS load is inserting records into the Table1. So i need to find if any duplicate records are inserting into table1. If inserted then i need to fire a trigger and need to insert that duplicate record into Table2.

Please suggest the solution. Your help really appreciated. Thanks in advance

subbu2464
  • 1
  • 2

2 Answers2

1

Your best option is to create a INSTEAD OF INSERT trigger on the table.

Inside the trigger you can check if the record already exists and insert it on another table or proceed with the insert.

lbarreira
  • 175
  • 10
0

Here's an example of the instead of trigger. I've assumed you only want one copy of each duplicate. (I've assumed 'Name' is the only key used in LoadTable for detection of duplicates)

CREATE TRIGGER tCheckDupes ON LoadTable INSTEAD OF INSERT
AS BEGIN
    INSERT INTO DupesTable(Name)
        SELECT DISTINCT i.Name FROM INSERTED i
            INNER JOIN LoadTable lt
            ON lt.NAME = i.NAME;

    -- Because this is an instead of trigger, you need to do the actual insert as well.
    INSERT INTO LoadTable(Name)
        SELECT Name FROM INSERTED i;
END

Fiddle here

StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • You should also add some condition to the insert on `LoadTable` to avoid inserting the duplicate records. – lbarreira Jan 22 '14 at 13:06
  • OP hasn't mentioned anything about NOT inserting the duplicates into the Load Table, although this would possibly be a requirement. I've +1'd your answer, BTW. – StuartLC Jan 22 '14 at 13:07
  • i have almost 80-90 columns on my table. If each and every column is same in the row then it should considered as duplicate. My question is trigger should be fired when a duplicate is inserted. This means if a row is already existed in the table and the same data is inserting into the table through SSIS load then a trigger should be fired and that row have to insert into the another table.NOTE: I am very new to triggers – subbu2464 Jan 22 '14 at 13:13
  • Triggers aren't [conditional](http://stackoverflow.com/questions/280904/trigger-to-fire-only-if-a-condition-is-met-in-sql-server) - your best bet is to exit early if it isn't applicable. w.r.t. 90 cols, I would strongly suggest computing a hash of your data before inserting it, and then including the hash in the load table. This way the trigger can just check the hashes, instead of having to compare 90 columns. Surely your data has some form of idempotence (e.g. transaction id or similar?) – StuartLC Jan 22 '14 at 13:17