Transferring Large Amounts of Data using Batch Inserts

Below is a technique used to transfer a large amount of records from one table to another. This scales pretty well for a couple reasons. First, this will not fill up the entire log prior to committing the transaction. Rather, it will populate the table in chunks of 10,000 records. Second, it’s generally much quicker. You will have to play around with the batch size. Sometimes it’s more efficient at 10,000, sometimes 500,000, depending on the system. If you do not need to insert into an existing table and just need a copy of the table, it is better to do a SELECT INTO. However for this example, we are inserting into an existing table. Another trick you should do is to change the recovery model of the database to simple. This way, there will be much less logging in the transaction log. The WITH (TABLOCK) below only works in SQL 2008. [cc lang=”sql”] DECLARE @BatchSize int = 10000 WHILE 1 = 1 BEGIN INSERT INTO [dbo].[Destination] –WITH (TABLOCK) — Uncomment for 2008 ( FirstName ,LastName ,EmailAddress ,PhoneNumber ) SELECT TOP(@BatchSize) s.FirstName ,s.LastName ,s.EmailAddress ,s.PhoneNumber FROM [dbo].[Source] s WHERE NOT EXISTS ( SELECT 1 FROM dbo.Destination WHERE PersonID = s.PersonID ) IF @@ROWCOUNT < @BatchSize BREAK END [/cc] With the above example, it is important to have at least a non clustered index on PersonID in both tables. Another way to transfer records is to use multiple threads. Specifying a range of records as such: [cc lang="sql"] INSERT INTO [dbo].[Destination] […]