Bulk Insert from table to table Bulk Insert from table to table sql sql

Bulk Insert from table to table

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.

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

Tool box menu

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

Data flow sourcesData flow destinations

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

You could do the

SELECT fieldnamesINTO DestinationTableFROM 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.