0

I need to ALTER a column but I don't want to drop the table or column and re-create it. Is there a way to achieve this?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
zooney
  • 341
  • 6
  • 25
  • You may have to add an identity column, and copy the data into the new column, then drop the old column and rename the new id column. – Mike Gardner Nov 14 '13 at 13:58
  • @Michael how do you copy the data into the new column? You can't use UPDATE against an IDENTITY column - only INSERT with SET IDENTITY_INSERT ON. Which means new table, not new column. – Aaron Bertrand Nov 14 '13 at 14:07
  • Use a temp table to hold your data while you reinsert everything. or BCP the data out --> add id column --> drop old column --> rename id column --> BCP data in. – Mike Gardner Nov 14 '13 at 14:19
  • You can do this as a metadata only change by creating a new table with the same schema but `IDENTITY` property then using `ALTER TABLE ... SWITCH` to change the table metadata. – Martin Smith Nov 14 '13 at 14:21

3 Answers3

0

There isn't a way to do this, sorry. If you want to add the IDENTITY property to an existing column, you're going to have to drop something (either the table or the column).

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
0

It is not possible without dropping column or table.

I suggest following scenario for making identity for column:

1) Drop all indexes/constraints 2) Create temporary table from your table 3) Delete all data in your table 4) Drop and recreate column with identity 5) Populate your table from temporary table (note you need use set identity_insert) 6) Create indexes and constraints you deleted 7) Drop temporary table

Vladimirs
  • 8,232
  • 4
  • 43
  • 79
-3

Yes you can do this using SQL Server Management Server. Go to Tools -> Options -> Design -> Table and Database Designers.

From here uncheck "Prevent saving changes that require table re-creation".

Refer to the image attached here

Weslyqw
  • 1
  • 2
  • 1
    The questioner specifically says " I don't want to drop the table or column and re-create" - this will recreate the table. Hence why required to uncheck "Prevent saving changes that require table re-creation" – Martin Smith Sep 02 '22 at 16:50