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.
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. |
|
|
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? |
|
|
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 |
|
|
|
|
|