none
SQL Merge Alternatives - 2millions records (SQL2005) RRS feed

  • Pergunta

  • Hi guys

    I have about 35 records that I would like to merge into a table that has potentially 2million records, i dont have an option of using merge since i am using SQL server 2005. 

    so ideally i want to

    check if a record exist or not

    if it doesn't exist check the values(not all values are elligible to be inserted) and insert

    if it exists check the values (if they are different) then update 

    What could be the best approach?

    domingo, 12 de janeiro de 2020 22:50

Todas as Respostas

  • insert into myTable (...)

    select columns from myRowsToInsertTable where not exists (....)

    UPDATE ... 

    from myTable T

    inner join myRowsToInsertTable I on ...

    where ...

    ---------------------------------------

    It will probably be slow but I don't think a single MERGE command will be much better on a big table in terms of performance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    domingo, 12 de janeiro de 2020 23:03
    Moderador
  • I should mention that the 35rows I have to merge are not part of table, they are provided by the application.

    each of these rows has to conditionally inserted or updated (i.e. check that it fullfills certain conditions before insert or update)

    domingo, 12 de janeiro de 2020 23:07
  • In this case you can either do code for each of these rows (same IF / ELSE) or first put them into temp table and do the process using the method above. It's up to you and doesn't really matter which approach you chose.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    domingo, 12 de janeiro de 2020 23:09
    Moderador
  • In this case you can either do code for each of these rows (same IF / ELSE) or first put them into temp table and do the process using the method above. It's up to you and doesn't really matter which approach you chose.

    For every expert, there is an equal and opposite expert. - Becker's Law
    My blog

    My TechNet articles

    I am guessing that execute a query from the App for each row will be much slower than import the rows from the app to temp table and work on the tables directly as Naomi first suggested.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    segunda-feira, 13 de janeiro de 2020 00:39
    Moderador
  • Hi TinasheChipomho, 

    Also, if you would like to use IF ...ELSE , please check following script. 

    if not exists(select 1 from myRowsToInsertTable where not exists (....))
    insert into myTable (...)
    select columns from myRowsToInsertTable where not exists (....)
    
    if exists(select 1 from myRowsToInsertTable where not exists (....))
    UPDATE ... 
    from myTable T
    inner join myRowsToInsertTable I on ...
    where ...(T.column<>I.column)

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    segunda-feira, 13 de janeiro de 2020 02:01
  •  

    Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    quarta-feira, 15 de janeiro de 2020 09:21