Insert into table select * from table vs bulk insert

I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ?

If no, Is there any way exclude index while inserting records. Process is inserting 150 Millions data in one execution.

We are planning to create stage table (Which will not have any index Table1), then transfer it from stage table to target table (Which will have index Table2)

We are not in the situation to create flat file from the process.

But we are looking something that can speed up process when we transfer data from Table1(not indexed) to Table2 (indexed).

Any way to use BulkInsert from Table1 to Table2?

Your best bet will be to use SSIS or BULK INSERT. There are various performance improvements that you can do when using them and they are very well documented in The Data Loading Performance Guide.

At SSIS level, you can look into below things to speed up data read and data load :

• Fast Parse Option along with its limitations.

• Use the SQL Server Native Client 10.x OLE DB provider for an In-Memory, high performance connection

• Set the Packet Size to 32767

• Select the OLE DB Destination Data Access mode “Table or View – fast load” option

Refer to Speeding Up SSIS Bulk Inserts into SQL Server for more details.

Below are some good ways to improve BULK INSERT operations :

• Using TABLOCK as query hint.

• Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.

• Changing the Recovery model of database to be BULK_LOGGED during the load operation.

• If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.

• Using Trace Flag 610 at the beginning of BULK INSERT operation.

The max degree of parallelism should be configured on the server rather than the default. You can refer to my answer on how it configure it here.

If you are using SQL Server 2014 then SELECT ... INTO is parallel.

Also, you should monitor Wait Statistics on the server especially SOS_SCHEDULER_YIELD resulting in scheduler contention on Servers having multiple CPUs running concurrent Bulk load operations and competing for the same CPU Cycles.

Also refer to :

• Bulk Inserting 1 Terabyte within 10 minutes

• We Loaded 1TB in 30 Minutes with SSIS, and So Can You

• Load 1TB in less than 1 hour

StackExchange Database Administrators Q#99367, answer score: 15