Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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