Add/Update an auto increment filed in table - MS SQL
Add/Update an auto increment filed in table - MS SQL
ALTER TABLE yourtablename
ADD yourID INTEGER IDENTITY NOT NULL,
PRIMARY KEY (”yourID “)
do you want to change the existing key as IDENTITY Key ??
You cannot use ALTER TABLE to change a column into IDENTITY column
Drop the column first and then recreate column as IDENTITY
ALTER TABLE Student
ADD CONSTRAINT StudentID_PK Primary Key (StudentID)
Option 2
Now, reload the table *but keeping the existing acctid values*.
SET IDENTITY_INSERT tableName ON
INSERT INTO tableName (acctid, …rest of columns…)
SELECT *
FROM tableNameTemp
SET IDENTITY_INSERT tableName OFF
– if PK designation has not been done yet
ALTER TABLE tableName
ADD PRIMARY KEY (acctid)
DROP TABLE tableNameTemp
