Insert huge amount of rows

Question

Insert huge amount of rows

jeelanik

SSC Eights!

Points: 953
More actions
November 17, 2010 at 7:32 am

#226767

Hello,
I need to insert millions of rows from one production database to another database on the same production server. I wanted to accomplish this in the most efficient way possible. Kindly forward tips or reference to materials that than guide me knowing what options I might have.
Thank you.
+ive

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

jeelanik (11/17/2010)
Hello,
I need to insert millions of rows from one production database to another database on the same production server. I wanted to accomplish this in the most efficient way possible. Kindly forward tips or reference to materials that than guide me knowing what options I might have.

1. set the recovery model of the target database to Bulk-Logged if it is Full.

2. Use Insert into table1(col list) select <col list> from db2.dbo.table2. This is a bulk logged operation.

OR

Keep db in simple recovery and insert records in batches.

Pradeep Singh

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 2

change your recovery mode to Bulked-logged before you do you insert in then turn it back after you are done.

There are many other questions.... this is going to cause locking, blocking and possibly dead locking. so you need to take that into consideration.

so you want to set this to off hours as well. if possible

Are this database running on the same disk? if so you need to take this into consideration as well. Same goes with T-LOGS. are theses on the same spindle?

make sure your database has enough room to grow before you do your insert so that the insert does have to stop and wait for database expansion each time it fills up

again there are many things so consider....these are just a few.

jeelanik SSC Eights! Points: 953 More actions · Answer 3

Thanks All.

With your guidance, I was able to dig out, http://msdn.microsoft.com/en-us/library/ms174335.aspx

Best Practices for Bulk Importing Data

Using INSERT INTO…SELECT to Bulk Import Data with Minimal Logging

You can use INSERT INTO <target_table> SELECT <columns> FROM <source_table> to efficiently transfer a large number of rows from one table, such as a staging table, to another table with minimal logging. Minimal logging can improve the performance of the statement and reduce the possibility of the operation filling the available transaction log space during the transaction.

Minimal logging for this statement has the following requirements:

The recovery model of the database is set to simple or bulk-logged.

The target table is an empty or nonempty heap.

The target table is not used in replication.

The TABLOCK hint is specified for the target table.

Rows that are inserted into a heap as the result of an insert action in a MERGE statement may also be minimally logged.

Unlike the BULK INSERT statement, which holds a less restrictive Bulk Update lock, INSERT INTO…SELECT with the TABLOCK hint holds an exclusive (X) lock on the table. This means that you cannot insert rows using parallel insert operations. For more information about locks, see Lock Modes.

jeelanik SSC Eights! Points: 953 More actions · Answer 4