0

I have a table which is having a column name ID field of integer type. It was declared IDENTITY. And it has data according to IDENTITY. But recently I removed IDENTITY column from that table. Now I want to change that to IDENTITY again. But this query says incorrect syntax

Alter table FuleConsumptions alter column  TransactionID INT IDENTITY(1,1);

But I can perform the same task using SQL server designer in properties of the table. What am I doing wrong here?

ChathurawinD
  • 756
  • 1
  • 13
  • 35
  • You cannot do this - just isn't possible. You must create a **new column** and add `IDENTITY` to it – marc_s Sep 26 '18 at 04:44
  • You'll need an extra table to make it work. See the first two answers to this question (depending on the size of your data set). [Adding an identity to an existing column](https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column) – Eric Brandt Sep 26 '18 at 13:36

1 Answers1

0

I think The identity column will hold the sequence of number thats why it thrown error better you drop column then create it again and set IDENTITY

Alter Table FuleConsumptions Drop Column TransactionID
Go
ALTER TABLE FuleConsumptions
ADD TransactionID int; 
go
Alter table FuleConsumptions alter column  TransactionID INT IDENTITY(1,1);
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63