What is the fastest way to copy data from one table to another

Question

I am having two tables, one is Staging and another one is Report. All processing happens in Staging and upon completion of such process I have to copy all the records to Report.

The Staging table contains millions of records so I just want to know what is the fastest way to copy this data to Report.

3 options which I know are:

• Insert into
• Select into
• creating a package and executing it via a job.

Any help in this regard is much appreciated.

Answer 1

another option is BCP out (queryout) and then BCP in/BULK INSERT

you can also use the BULK INSERT task in SSIS

Answer 2

Have a look at Transferring Data from One Table to Another

It discusses

• The INSERT INTO Method
• The DTS Import/Export Wizard method
• The BCP/Bulk Insert Method

Answer 3

Hi there,

You can give Table Diff a try:

http://www.volpet.com/table-diff/

It's done just for that.