SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Executing Insert statements in a batch / group
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bsethi24
Starting Member

India
25 Posts

Posted - 12/10/2012 :  14:44:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/10/2012 :  14:53:10  Show Profile  Reply with Quote
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

India
25 Posts

Posted - 12/11/2012 :  04:41:00  Show Profile  Reply with Quote
Dear All,

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

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/11/2012 :  08:18:16  Show Profile  Reply with Quote
like this:
http://sqlserverplanet.com/data-warehouse/transferring-large-amounts-of-data-using-batch-inserts
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000