Bulk Insert from table to table

Question

I am implementing an A/B/View scenario, meaning that the View points to table A, while table B is updated, then a switch occurs and the view points to table B while table A is loaded.

The switch occurs daily. There are millions of rows to update and thousands of users looking at the view. I am on SQL Server 2012.

My questions are:

• how do I insert data into a table from another table in the fastest possible way? (within a stored proc)
• Is there any way to use BULK INSERT? Or, is using regular insert/select the fastest way to go?

Answer 1

I'd be inclined to use SSIS.

Make table A an OLEDB source and table B an OLEDB destination. You will bypass the transaction log so reduce the load on the DB. The only way (I can think of) to do this using T-SQL is to change the recovery model for your entire database, which is far from ideal because it means no transactions are stored, not just the ones for your transfer.

Setting up SSIS Transfer

Create a new project and drag a dataflow task to your design surface

Double click on your dataflow task which will take you through to the Data Flow tab. Then drag and drop an OLE DB source from the "Data flow Sources" menu, and an OLE DB destination from the "Data flow Destinations" menu

Double click on the OLE DB source, set up the connection to your server, choose the table you want to load from and click OK. Drag the green arrow from the OLE DB source to the destination then double click on the destination. Set up your connection manager, destination table name and column mappings and you should be good to go.

OLE DB Source docs on MSDN

OLE DB Destination docs on MSDN