CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user3458120
user3458120

Reputation: 181

How to remove auto increment from table in sql server 2012

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?

Upvotes: 15

Views: 67161

Answers (6)

Brijesh Kavar
Brijesh Kavar

Reputation: 11

You can remove 'auto_increment' by using following query...

ALTER TABLE <table_name> MODIFY <auto_incremented_column_name> <column_data_type>;

For exmample,

table_name = STUDENT,

auto_incremented_column_name = ID,

column_data_type = INT

ALTER TABLE STUDENT MODIFY ID INT;

To add 'auto_increment'

ALTER TABLE STUDENT MODIFY ID INT AUTO_INCREMENT;

Upvotes: 1

Chris Sim
Chris Sim

Reputation: 4132

I searched a lot to find a simple solution to remove the auto increment because i should do a lot of work if i drop the column and the primary key which was a foreign key on another table where I should remove the data that are using my foreign ... finally I ended up with a very simple solution that made my life easy:

SET IDENTITY_INSERT <table_name> ON ;

Upvotes: 6

Goutham Arul
Goutham Arul

Reputation: 57

SET IDENTITY_INSERT [TABLE] OFF .. this allows to remove the auto increment to off state.., so that we have to enter the value in thatcolumn

Upvotes: -1

Avi Turner
Avi Turner

Reputation: 10456

The easiest way would be:

  1. Open SQL Server Management Studio.
  2. Locate Server > DataBase > Table.
  3. Right Click on the Table > Select Design.
  4. In the design window, Highlight the column you want to modify.
  5. In the Column Properties Window browse to Identity Specification > Is Identity And set to No.
  6. Go to the toolbar menu > Table Designer > Select Generate Change Script...
  7. Walla, you got the requested script.

I Like using this method for getting scripts, since it allows me to generate scripts I'm not sure how to compose from scratch and thus learning and improving my skills...

enter image description here

Upvotes: 15

dean
dean

Reputation: 10098

If it's a primary key column, then you have to drop the PK first. If there's any tables referencing it, then you'll have to drop these FKs to be able to drop the PK. After that, add another column of the same type, update it with values from identity column, drop the identity column, rename the new column to whatever the name of identity column was (with sp_rename procedure), recreate the PK, recreate the FKs, check if everything went right.

I'd be very careful doing it on a production database. Ensure that noone can access the data while you're doing this.

Upvotes: 6

Steve Pettifer
Steve Pettifer

Reputation: 2043

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';

Upvotes: 16

Related Questions