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?

Solution 1

You could to a Select ColA, ColB into DestTable_New From SrcTable. Once DestTable_New is loaded, recreate indexes and constraints.

Then rename DestTable to DestTable_Old and rename DestTable_New to DestTable. Renaming is extremly quick. If something turns out to have gone wrong, you also have a backup of the previous table close by (DestTable_Old).

I did this scenario once where we had to have the system running 24/7 and needed to load tens of millions of rows each day.

Solution 2

You could do the

SELECT fieldnames
INTO DestinationTable
FROM SourceTable

as a couple answers suggest, that should be as fast as it can get (depending on how many indexes you'd need to recreate, etc).

But I would suggest using synonyms in order to change the pointer from one table to another. They're very transparent and in my opinion, cleaner than updating the view, or renaming tables.

Solution 3

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

Comments

[user1044169]

Yes, I am currently doing ALTER VIEW, but I think I am going to switch to using synonyms per Sylvia's suggestion.

[user1044169]

Yes, the transaction log is a concern. Do you have any examples of how to setup a source table as an OLEDB source? The examples on the web are all about loading from text files... Thanks.

[Philip Kelley]

For straight SELECT * type functionality, synonyms would be more efficient. They're very useful for cross-database references, too.

[user1044169]

Actually, I may have to stick to views because views provide ALTER functionality where synonyms need to be dropped and re-created.

[user1044169]

I retract this -- views appear to be a better option because they provide for ALTER functionality where synonyms are limited to drop/create.

[Ali Razeghi - AWS]

He is asking for 'bulk' not regular load. The problem with this answer is that it will blow up your transaction log for large transfers. Doing it in batches using a where loop, CLR, SSIS, or BULK INSERT is preferred and sometimes the only solution.

[Furqan Hameedi]

@AliRazeghi, please go through the question again, the user has asked for the fastest way to insert data from one table to another table, regardless of simple sql, bulk insert, transaction log maintainability issues.

[Ali Razeghi - AWS]

I wasn't the one who downvoted your answer but millions of rows in a production system could cause LOTS of locking/blocking based on the isolation level and hardware. That could take a very long time. A select * into a from b would be the simplest answer but not the fastest.

[Amirhossein Yari]

This method is not bulk insert and generate a lot of logs

[Graham]

I'm late to the party, but....If you drop and create synonyms within a transaction, it will appear atomic