Hi Sergey,
One of the problems is, that in the existing system invoice #'s are assigned at print time, i.e. the records are inserted with an invoice # of null, and records with null values do exist in the client's data.
I actually tried to create an identity column, but it won't let me save the table. Other than that it would be a much better solution, because once the stuff is converted to SQL server they're not going back, so it's a one time thing.
I guess I could select the records with null values into a temporary table,
delete them in the original and then insert them again? That might work.
Any easier solutions?
>Hi Peter,
>
>I'm not sure why you can't use IDENTITY on that column. Having existing data wouldn't prevent you from doing that. Anyway, if you still want to use a separate table to store ID's, take a look at
Re: Creating unique record ID's Message #
901526.
>
>>Ideally, I'd like to generate invoice numbers as an auto-incremented field where the seed value is stored in a table.
>>
>>On insert the value should be seed value + 1 and the seed value should be updated accordingly.
>>
>>I've tried a few things (and failed miserably) and would appreciate any ideas on the subject.
>>
>>Just in case you're wondering: I can't start from scratch with an auto-incremented field because of inherited data.
>>
>>TIA
Peter Pirker
Whosoever shall not fall by the sword or by famine, shall fall by pestilence, so why bother shaving?
(Woody Allen)