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 |
|
venkath
Posting Yak Master
202 Posts |
 Posted - 2009-03-16 : 14:19:54
|
| Hi AllI am trying to insert 9 million records into a table which are being fetched using SELECT statement.I would like to do this data insert in batches to improve the performance. Is there a way I can do the Insert in batches. Help on this is greatly appreciated.Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-16 : 14:25:13
|
| yup. use ROW_NUMBER() function to generate a sequence number if you dont have a uniqued valued column. then use a loop and each time take a defined set of records from total using thisfield value |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-16 : 15:05:25
|
| BCP or SSIS give you the option to set Batch Size (might be called something else in SSIS). This will let you break up your import into smaller pieces. This will help keep the log file smaller as the transaction will be dealing with less rows. However this won't help your throughput al that much. To increase performance consider using the TABLOCK feature and order your datafile in the same order as your clustered index. Also remove or disable any indexes/triggers/constraints on your table as well.Mike"oh, that monkey is going to pay" |
|
|
|
|
|
|
Is there a way to insert the data in batches