Post reply

Changing Identity In Existing Column SQL Server 2000

  • July 20, 2007 at 12:29 pm

    #175423

    I would like to re-number all of the entries within my table so that the new number scheme is unique to that table. 

    I used the following command, but I keep receiving the below error message.

    ALTER TABLE DAWNTEST ALTER COLUMN [ID] int IDENTITY(500000,1) NOT NULL

    Server: Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'IDENTITY'.

    Any idea what I am doing wrong?  I issued the command via Query Analyzer.

     

    Many Thanks!

    Dawn

  • July 20, 2007 at 12:39 pm

    #721253

    Check BOL, DBCC CHECKIDENT.

    Looks like this:

    DBCC CHECKIDENT('DAWNTEST', RESEED, 500000)

  • July 20, 2007 at 1:35 pm

    #721267

    This command worked great for any new entry that was added to the table.

    However, It did not re-number the 8 entries that I already had in the table.

    I was trying to re-number ID #'s 1 thru 8 to 500000 to 500007.

    Any other suggestions??

    Thanks!

    Dawn

  • July 20, 2007 at 3:20 pm

    #721288

    Off hand, no, but that isn't what I thought you were asking for either in your original post.  I'll have to look at this a little more.

  • July 23, 2007 at 11:36 am

    #721589

    It's not elegant but:

    • create DawnTemp <according to new criteria>
    • insert into DawnTemp <everything but identity col>

      select <everything but identity col> from DawnTest

    • make sure everything went over all right
    • drop DawnTest
    • rename DawnTemp to DawnTest

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • July 24, 2007 at 6:49 am

    #721873

    Or just turn set identity = No for the column, renumber and turn identity = Yes

    Dave

  • July 24, 2007 at 7:44 am

    #721910

    also you can use the set identity_insert to on to insert values to indentity columns

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • July 24, 2007 at 7:45 am

    #721911

    Another way:

    1. Run DBCC CHECKIDENT('DAWNTEST', RESEED, 499999)

    2. Insert a second copy of the data into the table

    3. Delete the old data from the table.

    Here's an example:

    CREATE TABLE #DawnTest

    (

      id int IDENTITY(1,1)

    , mydata varchar(20)

    )

    INSERT #DawnTest (mydata)

      SELECT 'Row One'

      UNION ALL

      SELECT 'Row Two'

      UNION ALL

      SELECT 'Row Three'

      UNION ALL

      SELECT 'Row Four'

      UNION ALL

      SELECT 'Row Five'

      UNION ALL

      SELECT 'Row Six'

      UNION ALL

      SELECT 'Row Seven'

      UNION ALL

      SELECT 'Row Eight'

    SELECT * FROM #DawnTest

    DBCC CHECKIDENT('#DAWNTEST', RESEED, 500000)

    INSERT #DawnTest (myData) SELECT myData FROM #DawnTest

    SELECT * FROM #DawnTest

    DELETE #DawnTest WHERE id < 500000

    SELECT * FROM #DawnTest

    DROP TABLE #DawnTest

    -----------------------------------------------------------------

    If you have a lot of columns in the table, you can run this code to create a column list that you can cut and paste into the above query:

    DECLARE @columnList varchar(8000)

          , @tableName varchar(50)

    SET @columnList = ''

    SET @tableName = 'DawnTest'

    SELECT @columnList = @columnList

                       + CASE WHEN @columnList = '' THEN '' ELSE ', ' END

                       + c.name

      FROM sysobjects t

      JOIN syscolumns c

        ON t.id = c.id

     WHERE t.name = @tableName

    ORDER BY c.colid

    PRINT @columnList

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply