This is probably an easy one, but for some reason I cannot figure it out.
I have two tables: NewCustomer and CardNumber
NewCustomer: Holds new customer info. Gets populated by a batch process that adds hundreds to thousands of records per batch
CardNumber: Holds all existing card numbers, both available and already assigned cards.
What needs to happen is:
When a new customer is inserted into the newcustomer table it needs to get the next AVAILABLE card number from CardNumber. CardNumber then needs to set the available flag to 'N' for that card number.
is there an easy way to do this?
Is cursors the only option? is it an option?