Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Fast ways to load data from SQL server to SQL server RE: Fast ways to load data from SQL server to SQL server

  • June 13, 2018 at 12:00 pm

    #1993891

    Luis Cazares - Wednesday, June 13, 2018 10:29 AM

    Scott Coleman - Wednesday, June 13, 2018 10:05 AM

    A single 5-million row INSERT can be a huge problem, it may fill up the transaction log even if the database is in simple recovery.  A minimally-logged bcp insert is probably the best way to handle it as one file while minimizing this impact.

     FYI, SSIS does not generate a single insert when loading data to a table. Also, 5 million rows is not a huge insert that would take multiple hours. Even my laptop could handle that within a few minutes.

    Luis - it depends on the object used for the destination and the settings for that object.  The default settings for the OLEDB Destination will create a single transaction...but you can control that using the fast load option (bulk insert) and setting appropriate batch/commit sizes.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs