asked on

database re-design

Hi,

I'm working on a database that previous developers setup. I want to turn the PK on the tables into an identity w/ autoincrement, but I still need to keep the existing records, with existing ids.. What is the best way to go about doing this?

Thanks - Trevor

lluthien

hmm..
assuming that the actual value of the autoincremented number is not important.

i'd do this:
open the table in design mode.
add the column (preferably on the start )
set the column to allow nulls.
save the table

then, update the table to populate the numbers.
the because you need an ID for the PK,
assume the current id, so try this:

in query analyzer
update [mytable]
set mynewID = oldID

which basically copies the id's to the new column.
then, go back to edit mode set the new ID to not null,
Add the PK to the new Column.
and set autoincrement = true,
use the highest Old ID as the start of the autoincrement.

hope this helps

trevorhartman

ASKER

good idea.. but some of the existing PK's can't be convereted to an int (e.g. 1000002A)

the current PK is an nvarchar(10)

any ideas on how to overcome that?

thanks - Trevor

trevorhartman

ASKER

i think i'll probably have to end up having an oldId column or something to hold the old PKs... what a mess!

ASKER CERTIFIED SOLUTION

lluthien

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trevorhartman

ASKER

thanks for the advice :)

lluthien

glad to help

thx for the points