Geek Answers Handbook

Convert existing column to id

I have a table in SQL Server with a set of records. I want to convert the identity column, which is the Primary Key, to the identity column without data loss. I thought of the following two approaches:

  • Create a new table with the identifier and delete the existing table.
  • Create a new column with ID and release the existing column.

but it’s clear that they cannot be implemented, because maintaining records is my first priority.

Is there any other way to do this?

+7
sql sql-server tsql sql-server-2012
source share
4 answers

Since you are using SQL Server 2012, another possible alternative would be to create a sequence object that has an initial value of the highest ID +1 already in your table, and then create a default constraint for your column using GET NEXT VALUE FOR and refer to your only what created sequence object.

+2
source share

This solution violates your point 2, but there is no other way, and I think your goal is to preserve the old values, because nothing else makes sense ...

You can do the following:

  • allow you to insert into the identifier columns in the table:

    set identity_insert YourTable ON
  • add a new ID column to the table with the id and paste the values ​​from your old columns.

  • rotate the ID insert

     set identity_insert YourTable OFF
  • remove old id column
  • rename new column to old name
  • make it primary key

The only problem may be that your ID column is already connected as a foreign key to other tables. Then you had a problem with deleting the old column ... In this case, you need to discard the foreign key constraints in the identifier column after step 3, then perform steps 4 to 6, and then recreate the foreign key constraints.

+3
source share

If you have direct access to the server database, just go to the table design, select the PK column and change the identifier to “Yes”. Make sure you select a seed for the maximum value of this column. The threshold is 1 by default. Save the table design and you should be good to go. enter image description here

0
source share
 create table t1 (col1 int, col2 varchar(10)) insert into t1 values (10, 'olddata')

- add col id

 alter table t1 add col3 int identity(1,1) GO

- rename or delete the old column

 alter table t1 drop column col1

- rename the new col to the name of the old col

 exec sp_rename 't1.col3', 'col1', 'column' GO

- add a new test, view the table

 insert into t1 values ( 'newdata')

select * from t1

-2
source share

More articles:


All Articles