-2

I have an existing database, with a table Categories, where I have a primary key on a column id. I have a lot of rows in my database, so I don't want to make it from the beginning.

I forgot when I was creating it to do AUTO_INCREMENT in Categories, so now I try to update it, but I get an error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'MODIFY'.

This is my code

ALTER TABLE Categories MODIFY id INT AUTO_INCREMENT;

Does anyone know what I do wrong?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
johns
  • 183
  • 1
  • 14

2 Answers2

2

First create a new identity column called ID2

ALTER TABLE CATEGORIES
ADD COLUMN ID2 INT IDENTITY(1,1) NOT NULL;

Then populate it with values from ID

SET IDENTITY_INSERT CATEGORIES ON; -- thanks Mathese

UPDATE C
SET ID2 = ID
FROM CATEGORIES C
ORDER BY ID ASC;

SET IDENTITY_INSERT CATEGORIES OFF;

Once your ID values are safely migrated over to the new ID2 column, drop ID.

ALTER TABLE CATEGORIES
DROP COLUMN ID;
GO -- I think you may have to do GO here.

Then, rename ID2 to ID.

EXEC SP_RENAME @OBJNAME = 'CATEGORIES.ID2', @NEWNAME = 'ID', @OBJTYPE = 'COLUMN';
sam yi
  • 4,806
  • 1
  • 29
  • 40
  • When you will do the update you will receive an error because you cannot update an identity column unless specifying set identity_insert on categories – Mathese F Dec 18 '13 at 07:14
0
ALTER TABLE categories 
   ADD ID INT IDENTITY

and then you can make it as primary key

ALTER TABLE categories 
   ADD CONSTRAINT PK_categories
   PRIMARY KEY(ID)
vhadalgi
  • 7,027
  • 6
  • 38
  • 67
  • i get Column names in each table must be unique. Column name 'ID' in table 'Categories' is specified more than once. – johns Dec 18 '13 at 01:36
  • i already have it definied, so i can't do that – johns Dec 18 '13 at 01:40
  • 1
    You cannot add identity to an existing column. Best you can do is create a new Identity column (ID2) then copy data over from the ID column... drop ID column then rename ID2 to ID. Or you can follow this link http://www.mssqltips.com/sqlservertip/1397/add-or-drop-identity-property-for-an-existing-sql-server-column/ – sam yi Dec 18 '13 at 05:10
  • @samyi - ahem. It *can* be done without having to mess about with the data - you can use the [partitioning features](http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss/6086661#6086661) – Damien_The_Unbeliever Dec 18 '13 at 07:29
  • @Damien_The_Unbeliever, thanks for the headsup on that... fantastic! – sam yi Dec 18 '13 at 15:31