Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Location
    Philippines
    Posts
    11

    Question Unanswered: Copying tables with identity columns between DBs

    Hi,

    We are trying to copy data between databases. However we are having problems with tables with columns "generated by default as identity". Once data is transferred to another DB, the identity column doesn't detect that data already exists, thus causing a duplicate key error on the next insert. It would have been ok to just regenerate these values but they are linked to other tables as foreign keys.

    How could I work around this? Is there a way to set the "next available value" for identity columns?

    One way I thought of is to determine the MAX value of that column to be inserted, then insert that number of "dummy" records into the new table. Delete the "dummy" records, then insert/import the real data. A duplicate key error should no longer appear. However this seems like an extreme solution.

    Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use:
    ALTER TABLE mytable ALTER COLUMN identityCol restart with x

    where x is max+1 of current identity values.

    HTH

    Andy

  3. #3
    Join Date
    May 2002
    Location
    Philippines
    Posts
    11

    Thumbs up

    Quote Originally Posted by ARWinner
    Use:
    ALTER TABLE mytable ALTER COLUMN identityCol restart with x

    where x is max+1 of current identity values.

    HTH

    Andy
    Thanks Andy, that's exactly what I needed!

Posting Permissions

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