Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Executing Insert statements in a batch / group

Author  Topic 

bsethi24
Starting Member

25 Posts

Posted - 2012-12-10 : 14:44:13
Dear All,

Hi! I need to generate Insert scripts of more than 1 million records & execute the same on another server. The normal frequency is weekly but, but sometimes I have to run it twice / thrice in a week.

I need to know that how we do make batch of these insert statements so that instead of executing individual statements we can execute group of Insert statements. The batch/group can be of 2000 rows or 5000 rows to improve the performance.

I also need to know that will it increase the performance as we are inserting multiple records in a single batch?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-10 : 14:53:10
First, I would suggest that instead of generating insert scripts for over a million records, use another method - SSIS perhaps - to transfer the data. SSIS is pretty flexible and powerful, so it can handle very complex operations.

If you do want to stick with the insert statements, what you said - breaking it up into batches of 2,000 or 5,000 may be the thing to do. You can separate batches using the batch separator, which is GO (unless you have changed it).

Also, you may want to do frequent log backups to avoid log file growing large because of the inserts.
Go to Top of Page

bsethi24
Starting Member

25 Posts

Posted - 2012-12-11 : 04:41:00
Dear All,

How do I break these Insert statements if they comes under IF condition?
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 08:18:16
like this:
http://sqlserverplanet.com/data-warehouse/transferring-large-amounts-of-data-using-batch-inserts
Go to Top of Page
   

- Advertisement -