Use SQL Server> Tips & Skills> Development Skills to divide a newer into several batches
Update Date: April 14, 2003
This tip is taken from the first phase of SQL Server magazine. For more techniques, please visit the SQL Server's Skills Center. Q: I want to update a large table with a 5,000 line manner, but I don't know how to split data. The table does not include incremental numbers or integer keys. How can I achieve data updates while maintaining good performance? A: If you know which rows have not been updated, you can use simple predicates to exclude updated rows, RowCount settings can help you batch data. The following code list describes how to use this setting:
Set Rowcount 1000 While (1 = 1) Begin Begin Transaction Update ... set ..., mylastupdate = 'Date', ... where mymelastupdate <'date' - Update 1000 Uninomed line if @@ rowcount = 0 Begin Commit Transaction Break End Trait Transaction End
Return after the specified number of rows, RowCount will cause SQL Server to stop query processing. This technology is useful because it avoids a lot of updates, and the fewer numbers in the update, the smaller the possibility of other users cannot access the data. Combined with transaction log backup, this method can also minimize the size of your transaction log. If you do not identify a mechanism for updated rows, you can use the cursor to traverse all data and submit each X value. However, the time of the cursor occupies the server resources is usually more than the collection-based statement. -Microsoft SQL Server Development Team returns to use skills pages.
