Commit every 10000 rows

[Former Member]

Good afteroon in my timezone.

I want to update a table , the RDBMS used is the the Sybase ASE 15. Because the table contains almost 1 million rows , and i have to run this update in Production environment, i want to update and commit every 10000 rows.I do not have experience in Sysbase. Can anyone help me, if possible putting some code example

Thanks in advance Best Regards

[MarkCulp]

This forum is for questions relating to SQL Anywhere, not ASE. Please refer to the FAQ and ask your question on the SAP ASE forum. (see last question in the FAQ for link)

---

Having said the above, if I were writing such a program I would simply keep a count of the number of inserts/updates (or bulk inserts/updates since that would be more efficient) and issue a commit every N rows (N equal to 10000 in your case if you are issuing 1 insert/update per request... or 10000/x if you are issuing bulk inserts/updates of x per request).

HTH

[VolkerBarth]

In case you want to make your update via plain SQL and you can distinguish "already updated" and "still to be updated" rows by a simple condition, a simple loop with a UPDATE TOP statement comes handy, such as:

lbl:
LOOP
   UPDATE 10000 YourTable SET ... WHERE <not already modified>;
   IF @@ROWCOUNT < 10000 THEN
      LEAVE lbl;
   END IF;
   COMMIT;
END LOOP lbl;
COMMIT;

Note: This is in SQL Anywhere Watcom-SQL syntax, however, AFAIK, ASE does support UPDATE TOP..., too, so this could be adapted to T-SQL syntax.

[VolkerBarth]

Sorry, it should read "UPDATE TOP 10000 ...", and I should not post untested code:(