Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2007
    Posts
    14

    Unanswered: Removing Duplicates to create a primary Key

    Hi, I'm in the midst of an Access 2003 to SQL server 2000 upsizing project and have come across a table on Sql Server that has a field that looks like it's supposed to be the PK but it contains duplicates. What I'd like to do is to have a cursor start at the first value and increment the next value by 1. Could someone explain how I'd go about this?

    Many thanks,
    Peter

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if the column contains dupes, then it cannot be the PK right now

    so you should be able to alter the table and add an identity column

    after adding the identity column, update the table, and set your column equal to the identity column, then alter the table and drop the identity column

    alternatively, leave the identity column and drop the original one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Posts
    14
    Thanks r937, I'll give it a go.

  4. #4
    Join Date
    Dec 2007
    Posts
    14
    Turned out the field I was interested in was already an Identity column. I didn't realise Identity columns could contain dups. Anyway to sort this out I dropped the column and Added it again using:

    ALTER TABLE tblDailyOperatingData_test
    Add DODindexID [int] IDENTITY (997701261, 1) NOT NULL
    go

    Thanks,
    Peter

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    interesting choice of seed value, may i ask why that particular number?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2007
    Posts
    14
    Yep, I thought so too. That's what was in the create table statement. As I mentioned I'm taking over this project from a previous developer so only he really knows.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by PeterOC
    I didn't realise Identity columns could contain dups.
    ...only if SQL Server's restraint is circumvented.
    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
  •