Menu
I have created a table in SQL Server 2012 with primary key as auto increment. But how can I remove that auto increment property from the table using a SQL query?
287
You don't want to drop the column but moving forwarding you don't need to have auto increment, instead you would like to insert the values in column manually. To disable or remove the auto increment from a column in MySQL Table, you can simply Modify the column with same data type but without auto_increment clause.
If you're looking to add auto increment to an existing table by changing an existing int column to IDENTITY , SQL Server will fight you. You'll have to either: Add a new column all together with new your auto-incremented primary key, or. Drop your old int column and then add a new IDENTITY right after.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
If you need to keep the data in that column then create a new column on the table which is of the same type (but a different name), copy the data from the column you want to get rid of to the new one, drop the old column and rename the new. Complete example:
CREATE TABLE test(col1 INT IDENTITY (1,1) NOT NULL, col2 VARCHAR(10) NULL);
ALTER TABLE test ADD col3 INT NULL;
UPDATE test SET col3 = col1;
ALTER TABLE test DROP COLUMN col1;
EXEC sp_rename 'dbo.test.col3', 'col1', 'COLUMN';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
