Bulk Loading a Table without Taking it Offline - SQL Server

Overview¶

A common situation when needing to bulk load a table (or set of tables) is how to do and still keep the production environment up 24 x 7. The following article outlines a solution.

Solution for a Table¶

Setup Work¶

Create a table with the same schema as table you're wanting to load. In our example, MyTable is the table containing the "live" production data, so we create the MyTableStaging table with an identical schema

Loading the Table¶

Step 1 - Load data to the staging table
In our case, we would bulk load our data to the MyTableStaging table.

Step 2 - Swap the two tables
This is done through a sequence of three calls to the sp_rename system stored procedure, which typically executes sub-second. It is important to wrap this sequence inside a transaction to prevent the production system from potentially "losing" the live data. (Note that you wouldn't actually lose the data; it would simply be in a table of the wrong name for the production system to access it.)

begin Tran
begin try
    execute sp_rename 'MyTable', 'MyTableTemp'
    execute sp_rename 'MyTableStaging', 'MyTable'
    execute sp_rename 'MyTableTemp', 'MyTableStaging'
    Commit Tran
end try
begin Catch
    rollback tran rename
end catch

Solution for a Database¶

The same strategy can be used with an entire database. The SQL script would be changed as follows.

{copytext|div1}

exec sp_rename 'MyDb', 'MyDb_Temp', 'DATABASE'
GO
exec sp_rename 'MyDb_Staging','MyDb', 'DATABASE'
GO
exec sp_rename 'MyDb_Temp', 'MyDb_Staging', 'DATABASE'
GO