Home Forums SQL Server 2012 SQL Server 2012 - T-SQL How to quickly update/insert 3M records in a large table? RE: How to quickly update/insert 3M records in a large table?

  • March 29, 2015 at 2:10 pm

    #1787180

    George Wei (3/28/2015)

    Dear all,

    Our system runs a SQL Server 2012 DB, it has a table (table_a) which has over 10M records. Our system have to receive data file from previous system daily which contains approximate 3M updated or new records for table_a. My job is to update table_a with the new data.

    The initial solution is:

    1 Create a table (table_b) which structur is as the same as table_a

    2 Use BCP to import updated records into table_b

    3 Remove outdated data in table_a:

    delete from table_a inner join table_b on table_a.key_fileds = table_b.key_fields

    4 Append updated or new data into table_a:

    insert into table_a select * from table_b

    As the test result, this solution is very inefficient. Step 3 costs several hours, e.g. How can I improve it?

    George

    Except for the merge process you describe above, it the table static? In other words, is there anything else other than the process above that causes any insert/updates/deletes to the table at all?

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