database re-design

Posted on 2005-05-10
Last Modified: 2010-03-19

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
Question by:trevorhartman
    LVL 11

    Expert Comment

    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

    LVL 8

    Author Comment

    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
    LVL 8

    Author Comment

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

    Accepted Solution

    a way to overcome this..

    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.
      update mytable set newprimarykey = @myid
      set @myID = @myID + 1

    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

    LVL 8

    Author Comment

    thanks for the advice :)
    LVL 11

    Expert Comment

    glad to help

    thx for the points

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now