Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

i have some big tables with several million records and want to change the identity(1,1) to identity(1,5). what would be the best way to do that?

share|improve this question

migrated from superuser.com Apr 25 '10 at 15:48

This question came from our site for computer enthusiasts and power users.

add comment

1 Answer 1

up vote 0 down vote accepted

No answer yet?

i see a way like this on a in place migration:

SELECT *
INTO Backup_Table
FROM SOURCE_Table
GO
DROP Source_Table
GO
CREATE Source_Table (SerialNo IDENTITY(1,5) NOT NULL, ...)
GO
INSERT Source_Table(*)
SELECT *
FROM Backup_Table
GO

This has to be done for all the tables in a database.

What do you think?

share|improve this answer
    
On this approach one have to recreate all the constraints and indexes for new... The better way is to get a full-SQL-script from the table and change the Value of identity-step from 1 to 5 with an editor (search & replace). But how to restore the data? –  Ice Jul 25 '10 at 10:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.