Post reply

General approach BULK INSERT, SELECT INTO, RENAME

  • November 8, 2014 at 9:21 am

    #294992

    My stored procedure has the following steps (runs every 2 hours):

    1. TRUNCATE StagingTable and DataTable

    2. BULK INSERT into StagingTable (800.000 rows)

    3. INSERT INTO DataTable (joins to other tables, updates etc.) from StagingTable.

    All works fine, takes ~2 minutes to run.

    During some of this time the DataTable is not available to Excel for reports. Which is not fine.

    Modified the process as follows:

    1. TRUNCATE StagingTable

    2. CREATE tempDataTable

    3. BULK INSERT into StagingTable (800.000 rows)

    4. INSERT INTO tempDataTable (joins to other tables, updates etc.) from StagingTable.

    5. DROP DataTable

    6. RENAME tempDataTable to DataTable.

    For some reason this last process runs twice as fast. The DROP / RENAME takes less than a second. So the reporting system (MS Excel) should have access to the data on the SQL Server.

    It seems to work fine. I tried "SELECT * INTO dbo.DRPDATA FROM tempDRPDATA" before, which takes about 2-7 seconds.

    Q: is there any reason not to use the DROP/RENAME process?

    TIA,

    Julian

    Netherlands

  • November 8, 2014 at 10:06 am

    #1757309

    Quick thought, nothing wrong with the DROP/RENAME approach, just remember to re-create any foreign key constraint etc. if applicable. Another way of doing this is to switch partitions, normally quicker if done properly.

    😎

  • November 8, 2014 at 10:24 am

    #1757310

    Thank you.

    Cheers,

    J.

  • November 12, 2014 at 4:44 am

    #1757911

    I've seen a similar approach used in the past when populating a large data warehouse that took several hours. A copy of the database was created and populated, and then the tables were dropped/renamed as per your idea.

    The only snag that we could see was that if any queries were running on the live tables when they were dropped then obviously they'd fail and need to be re-run, but since this method meant the database was only unavailable for a few seconds while being dropped/recreated rather than all morning while it was being repopulated, we figured it was an acceptable side-effect.

  • November 12, 2014 at 4:57 am

    #1757917

    Thanks.

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

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