• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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
0
trevorhartman
Asked:
trevorhartman
  • 3
  • 3
1 Solution
 
lluthienCommented:
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

0
 
trevorhartmanAuthor Commented:
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
0
 
trevorhartmanAuthor Commented:
i think i'll probably have to end up having an oldId column or something to hold the old PKs... what a mess!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lluthienCommented:
a way to overcome this..
hmm

not too happy with cursors myself, but they actually come in handy here i guess..

something like this:

--declare cursor and new ID
DECLARE @myID as int
set @myID = 1
DECLARE my_cursor CURSOR FOR
SELECT currentprimarykey FROM mytable
ORDER BY currentprimarykey

-- open cursor
OPEN my_cursor
-- initialize
FETCH NEXT FROM my_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
  update mytable set newprimarykey = @myid
  set @myID = @myID + 1
END

CLOSE my_cursor
DEALLOCATE my_cursor

didnt try this, but i think you catch my drift..

something like this instead of the update statement i mentioned before.
that should work.

don't have QA here, so i cant check the syntax i'm afraid

hth
0
 
trevorhartmanAuthor Commented:
thanks for the advice :)
0
 
lluthienCommented:
glad to help

thx for the points
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now