If not CURSORS ?

I have a requirement to check a value which MUST be unique forever even if it is removed and readded.I have created a seperate table to maintain this value.

Without using a cursor how would I be able to append a duplicate base value (i.e. smith.j@.here.now) with the next sequential value (i.e. smith.j02@.here.now)

Any takers?

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.Concatenate the datetime(getdate()) down to 1/1000 second. I am sure it will be unique. That's most of the spam mailers do when they create a fake ID to get around your blocking.|||Better yet, concatenate newid(). That will guarantee you a unique value all the time.|||Originally posted by joejcheng
Better yet, concatenate newid(). That will guarantee you a unique value all the time.

It also has to be sequential, not just unique|||You can use substring and max functions to achieve the same sequentially.|||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 smith.j02@.here.now, smith.j03@.here.now, and smith.j04@.here.now already exist, there is simply no way to know whether smith.j05@.here.now was not previously created and deleted. You have to store the used values permanently somewhere.|||...if you create a table with two columns:

EMailPrefix varchar(50),
EMailIncrement int

...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...|||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:

EMailPrefix varchar(50),
EMailIncrement int

...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...

Unfortunate|||Use a cursor in combination with the table of historical values.