Check Data exists or not

  • HI all,

    I have a scenario in which I have to transfer the data from tableA to tableB.

    But before inserting in tableB, I have to check that record from tableA exists or not.

    I know I can use IF EXISTS for that but if records are 1000 or more then it will take time to check whether data exists or not.

    Is there any alternate way for this..

    please suggest

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • INSERT INTO TableB ...

    SELECT ...

    FROM TableA

    WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (11/29/2013)


    INSERT INTO TableB ...

    SELECT ...

    FROM TableA

    WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>)

    Sorry to mention that I also have update the record if data from tableA already exists in tableB....

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Will it be good if I good if I use a MERGE for this and use that MERGE in a stored procedure, if MERGE not effects the performance ?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (11/29/2013)


    GilaMonster (11/29/2013)


    INSERT INTO TableB ...

    SELECT ...

    FROM TableA

    WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>)

    Sorry to mention that I also have update the record if data from tableA already exists in tableB....

    Merge is designed to do exactly that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (11/29/2013)


    kapil_kk (11/29/2013)


    GilaMonster (11/29/2013)


    INSERT INTO TableB ...

    SELECT ...

    FROM TableA

    WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>)

    Sorry to mention that I also have update the record if data from tableA already exists in tableB....

    Merge is designed to do exactly that.

    Will it effect the performance if data is more as it also process row by row only..

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (11/29/2013)


    GilaMonster (11/29/2013)


    kapil_kk (11/29/2013)


    GilaMonster (11/29/2013)


    INSERT INTO TableB ...

    SELECT ...

    FROM TableA

    WHERE NOT EXISTS (SELECT 1 FROM TableB where <column comparison>)

    Sorry to mention that I also have update the record if data from tableA already exists in tableB....

    Merge is designed to do exactly that.

    Will it effect the performance if data is more as it also process row by row only..

    Merge doesn't process row by row, no more than insert, update or delete do.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Be very careful if you decide to use MERGE. There are several CONNECT items that are open about it and, like anything else, if you don't obey some fairly strict common sense rules about how to use it, it will burn you with no errors reported.

    --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)

  • can you please tell me how merge doesn't process row by row...

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk (11/30/2013)


    can you please tell me how merge doesn't process row by row...

    Actually, everything in SQL Server processes "row by row". If you let SQL Server do it, it's fast. If you try to tell SQL Server how to do it, it's slow. If you look at SELECT, INSERT, UPDATE, DELETE, or MERGE, do you see anything in there that would allow you to control the looping in the background?

    MERGE is no different than the 4 other basic statements when it comes to processing.

    --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)

Viewing 10 posts - 1 through 9 (of 9 total)

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