commit transaction - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

commit transaction

Author

Topic

alla2611
Starting Member

5 Posts

Posted - 2004-05-13 : 13:29:30

I need to commit every 10000 loaded transaction during DTS Load.
How could I do it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-13 : 13:30:28

SET ROWCOUNT 10000

UPDATE/INSERT/DELETE

COMMIT

Loop to the beginning until no more rows to modify.

Or in the transform data task properties, go to Options tab and specify a batch size. According to Books Online:

By default, the batch size is set to 0, which means that all the rows copied from the source are placed in a single batch before being submitted to SQL Server.
For example, if your source contains a million rows of data, and the batch size is set to the default, the data will not commit until the one-millionth row is processed. In cases such as these, you may want to commit the data in batches of one thousand, or ten thousand, rather than in a single batch of one million.

Tara