Avoiding insertion of duplicate rows

  • I have a table with data. I need a query that need to insert only new rows instead of whole data.

    Source Table:

    TableA

    FName

    LName

    PhoneNo

    Destination Table:

    TableB

    ID

    FName

    LName

    PhoneNo

  • Insert into base...

    SELECT FROM qry .... WHERE NOT EXISTS (SELECT * FROM base where qry.id = base.id)

  • If you don't care to track what the dupes are, just put a UNIQUE index on the columns with the IGNORE DUPES option and do the inserts.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • insert tableb(lname,fname,phoneno)

    select a.lname,a.fname,a.phoneno

    from tablea a

    left join tableb b on a.lname=b.lname and a.fname=b.fname and a.phoneno=b.phoneno

    where b.id is null

    A longer version of Ninja's suggestion...

  • If all you need to do is an insert, then the INSERT INTO... with a NOT EXISTS (or a LEFT OUTER JOIN) to check that the record isn't already there should do fine. You could also do it with a MINUS, but I don't find that as flexible.

    If you need to do an insert if the record doesn't exist at all, or an update if the key for the record is there but some of the other columns may differ, I'd go with Joe Celko's suggestion and look at using the MERGE statement.

    Having said that, in a system I built recently, all of the data updates were done via generated MERGE statements. So from experience, MERGE works perfectly fine as just an insert.

  • hi

    procedure with Example.

    Avoid Duplicate Insertion using sql server

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply