Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

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

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: If not CURSORS ??

    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.

  3. #3
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: If not CURSORS ??

    Better yet, concatenate newid(). That will guarantee you a unique value all the time.

  4. #4
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Re: If not CURSORS ??

    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

  5. #5
    Join Date
    Sep 2003
    Location
    Mumbai, India
    Posts
    36
    You can use substring and max functions to achieve the same sequentially.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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 it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...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....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53
    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

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use a cursor in combination with the table of historical values.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •