Convert an existing Column to Identity

 

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

  • Create a new table with identity & drop the existing table.
  • Create a new column with identity & drop the existing column.

    • but it's clear that they can not be implemented because keeping records is my first priority.

    Is there another way to do this?

    在使用SQL Server 2012时，另一个可能的替代方案可能是创建一个序列对象，该对象具有表中最高ID +1的起始值，然后使用GET NEXT VALUE FOR为列创建默认约束并引用您的您刚创建的序列对象。

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

    You could do the following:

  • make it possible to insert into identity columns in your table: 

    set identity_insert YourTable ON
  • add a new ID column to your table with identity and insert the values from your old columns

  • turn identity insert off 

    set identity_insert YourTable OFF
  • delete old ID column
  • rename new column to old name
  • make it to the primary key

    • The only problem could be that you have your ID column already connected as foreign key to other tables. Then you have a problem with deleting the old column... In this case you have to drop the foreign key constraints on your ID column after step 3, then do step 4 to 6 and then recreate your foreign key constraints.

    If you have direct access to the Server Database, just go into the design of the table, select the PK column, and change the identity to "Yes". Make sure you set your seed to the max value of that column. The increment is 1 by default. Save the table design and you should be good to go. 在这里输入图像描述

    链接地址: http://www.djcxy.com/p/16934.html

    上一篇: 使用连接的SQL更新查询

    下一篇: 将现有的列转换为标识