Hi All, I need to alter a existing column to IDENTITY in a table, but it is not happening. I mean in my table "Employee", Employee_ID column is previously INT type but now I want to make it IDENTITY type in sql script. with out using Management Studio, I need a script and I wrote a script but it is showing "ERROR" below. Please advice Cheers! Siona.
It won't work this way. Add another column with the identity property to the table Copy over the data from the old column to the new column using identity_insert = ON drop the old column rename new column to old columns name
But Frank, I have a case where I don't want to insert new column as IDENTITY Column in my table Lets say in my Employee_ID is already contains IDENTITY type of Data and more over it has some dependancy with other database table. In this time what you will do? If you creat a new column may be data changes and dependancy will go. then.... Please Suggest. Thanks Siona.
Do you mean that you already have an identity column in the table or that you have data that you want to use in the new identity column? You can have only one identity column per table. One step I mentioned was to "migrate" the data from the old non-identity column to the new identity column. So, your existing data would be preserved and is not lost. If there are constraints pointing to the old non-identity column they would have to be dropped before you can drop the column and need to be recreated on the new identity column. Sorry, but there is no easier way to achieve this.
Addition: The "data migration" would have to be done using the SET IDENTITY_INSERT <your table> ON setting. This is the only way to insert custom values into an identity column.
Frank, I didn't mean to create two identity columns which are not possible. And when you say Sorry, but there is no easier way to achieve this but there should have any way.. right? 2nd option is for data migration which i am ok with this option. But I asked altering the existing column to make IDENTITY type. Which i am not sure it can be achieved... If you suggest any other option would be great. Thanks, Siona.
If there would be an easy and straight ALTER TABLE ... ALTER COLUMN... IDENTITY, I would have suggested this, but there isn't. Actually you can do this in Management Studio in the design view for the table. But instead of saving the change, let SSMS generate the change script for you. There you can see what it is doing behind the scenes. []