I have to update a counter field in one table while pulling the next counter number from another table and update that table. For example:
Table 1) SYSCNTRL has a field called INDXCNTR
Table 2) ITEMMSTR has item numbers and needs the next available INDXCNTR from SYSCNTRL. This field is also called INDXCNTR
So as I loop through records in ITEMSTR I need to get the value of SYSCNTRL.INDXCNTR and assign it to this record. Immediately afterward I need to increment the value of SYSCNTRL.INDXCNTR by one. Then I continue the process until all records are updated.
I have done this before with cursors but I am trying to move away from using cursors.
This is for SQL Server 2005.