CODEWITHSUNDEEP
sql-serverresetidentity-column
user634036
user634036

Reputation: 109

rearrange the identity column value in sql server

Please help me! I have a table will a identity field the value are, for example, 1, 2, 4, 6, 8, 10, 11 .... etc.

I want to make it in a sequence, that is, 1,2,3,4,5,6,7... without using drop

Thanks

Upvotes: 2

Views: 5358

Answers (5)

Ramamoorthi.S
Ramamoorthi.S

Reputation: 11

I think that's best solution.. try if you like it…

DECLARE @INC AS INTEGER 
SET @INC = 1 
WHILE (@INC <= 7582) --7582 is rowcount of table
BEGIN
    UPDATE YourTable
    SET    ID = @INC
    WHERE  ID = (
               SELECT MIN(ID) AS T
               FROM   YourTable
               WHERE  ID > @INC
           ) --ID=> Identity column
    SET @INC = @INC + 1
END

Note: before executing that you need to remove the identity insert to the column.

Upvotes: 1

Tom_Granados
Tom_Granados

Reputation: 1

DBCC CHECKIDENT (<'Table Name'>, RESEED, <'last sequence in table**'>)

** this will make the number plus 1 (+1) when entered. i.e needed the next to be 38 for new record so used 37 as this value.

Upvotes: 0

M.R.
M.R.

Reputation: 4847

Why don't you create a new field, make it idenity seed starting from 1, and then delete the old column and rename the new column (and then recreate all your FK references).

Upvotes: 1

Fredrik E
Fredrik E

Reputation: 1838

There is no easy way to do this, and there is no reason why you should have to. You could however contruct a view to provide similar functionality:

CREATE VIEW MyTableView AS SELECT ROW_NUMBER() OVER (ORDER BY Identity) AS RowNum, * FROM MyTable

and then have your clients select from MyTableView instead.

If you need the view to have the same name as the table, so as to ensure backwards compatibility you can of course rename your table and then create a view with the old table name.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453910

You can't. identity columns are not updatable. (or at least you can't in any sane way. You could delete all rows from the table then reinsert them with IDENTITY_INSERT on)

Also gaps in identity columns are just a fact of life that it's best to come to terms with! If you need a contiguous sequence of numbers for display purposes you can use ROW_NUMBER.

Upvotes: 3

Related Questions