Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Bulk Loading a Table without Taking it Offline - SQL Server

RSS
Modified on Mon, Jun 28, 2010, 6:33 PM by Administrator Categorized as 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.