Convert string id of column to int autoincrement

Question

Hi,

I have table Cars

Id nvarchar(25) PK
Name nvarchar(max)

And there is some records.

Id      Name
CodeXYZ Namezxc
CodeQAZ Nameasd
CodeEDC Nameqwe

I want to convert Id column to int autoincrement:

Id      Name
1 Namezxc
2 Nameasd
3 Nameqwe

but I have no idea how to make it :/ Could you help me ?

Answer 1

Create a new table (TBLB) that matches the table structure of the first table (TBLA) except create the second table with an int identity insert column for your ID column.

Then

INSERT INTO TBLB (Name) SELECT Name FROM TBLA

Make sure that you map the columns so that you aren't selecting or inserting the Id column.

Then you can drop the original table and rename the new table.

It's a bit painful but should get you what you want. You can even add a column to TBLB to keep the original id in a new column.

Answer 2

In SQL Server Management Studio, you can right click on the table, and modify it.

Once in there, select the column you want to auto increment. You should be able to change it to an INT.

Then look at the column properties; one of them is IDENTITY, which is a unique and auto-incrementing field. By turning that property on, and saving the changes, you get what you want.

If you have duplicates in that field though, or values that can't be cast to an INT, it will fail. You would need to run an UPDATE on the table to get rid of any problems or duplicates first.

EDIT:

You made a comment "all the records are strings". If the string values don't cast to an INT, you can't have waht you're asking for. A field looking like "AAABC" can't be auto-incremented.