10

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?

Improve this question
3
  • You realize that going from a Table1 (heap) to Table2 (with indexes) is the same exact thing as just inserting into the table. In fact, you're doing MORE work by this methodology because you're inserting into a heap then into the table with indexes. That's 2x the inserts. I'd try just dropping the indexes, do your insert, then build the indexes again once the data is in.
    – Kris Gruttemeyer
    May 1, 2015 at 18:38
  • If you are not constrained by transaction log space, a single INSERT...WITH(TABLOCKX)...SELECT statement will perform well. Otherwise, you'll need to resort to SSIS. Depending on the number of non-clustered indexes and percentage of rows inserted, it may be best to disable the non-clustered indexes and re-enable afterwards.
    – Dan Guzman
    May 2, 2015 at 16:55
  • Yes. An insert into table2 select * from table1 acts very much like a bulk insert. Only works when the tables are identical I believe.
    – Maria nardi
    May 11, 2018 at 10:20

1 Answer 1

Reset to default
15

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 :

  1. Using TABLOCK as query hint.
  2. Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
  3. Changing the Recovery model of database to be BULK_LOGGED during the load operation.
  4. If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
  5. 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 :

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.