Oh ya, These values are not manually entered but populated through a DTS script. The existing values are repopulated from historic tables and new entries are added automatically. Initially the values would be populated without a number but a number needs to be generated on duplicates.
You can't do this with sequential values if you insist on the stipulation that a record can be removed from the database and readded at another time. Even using a cursor to find out that firstname.lastname@example.org, email@example.com, and firstname.lastname@example.org already exist, there is simply no way to know whether email@example.com was not previously created and deleted. You have to store the used values permanently somewhere.
If it's not practically useful, then it's practically useless.
Unfortunately it is possible that more than one new entry can be made in the same import. (i.e. smith.j exists and two more smith.j are imported).
The list of historic values are being saved in a seperate table as described without the "EMailIncrement int" field. I had considered your exact solution but did not know how to increment 2 newly added values with different increments.
Originally posted by blindman
...if you create a table with two columns:
...to store both parts of the e-mail address, it is a simple matter to
select EMailPrefix + cast(Max(EMailIncrement) + 1 as NewEMail from UsedEmails where EMailPrefix = @NewSubscriber group by EMailPrefix
...to get a new unused E-mail variation. Not sure if the syntax above is correct, but you get the picture....