Alter sql server table Issue

Question

I already created table in database. Now, I need to add "Identity" Column. Please suggest.

Create Cus(id int Pk,Name varchar2(50),Age int);

insert into Cus(id,Name,Age) values (1,'abc',12);
// here i need to add "Identity"
alter table Cus alter column id Identity(1,1)

This has been asked before: https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column — Rodney Ellis, Aug 29 '19 at 05:09
And: https://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx — Rodney Ellis, Aug 29 '19 at 05:11
Possible duplicate of [Adding an identity to an existing column](https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column) — Prashant Pimpale, Aug 29 '19 at 05:17

score 1 · Answer 1 · answered Aug 29 '19 at 05:10

1

You cannot use Alter command to add an identity to the table.

answered Aug 29 '19 at 05:10

Neeraj Agarwal

1,059
6
5

score 1 · Answer 2 · answered Aug 29 '19 at 05:13

1

Here, you need to create dummy column and drop existing one.

Create table Cus(id int ,[Name] varchar(50),Age int);

insert into Cus(id,[Name],Age) values (1,'abc',12);

Alter Table Cus Add dummyid int identity(1,1)

Alter Table Cus Drop Column id

Exec sp_rename 'Cus.dummyid ', 'id', 'Column'

answered Aug 29 '19 at 05:13

Ed Bangga

12,879
4
16
30

Thank you, "id" i already used as Foriegn Key with out deleting if possible – narayana reddy Aug 29 '19 at 06:10
well you need to delete data on foreign tables, no way you can do this. – Ed Bangga Aug 29 '19 at 06:11
it is production for clients creating issue – narayana reddy Aug 29 '19 at 06:18
you can read about UPDATE CASCADE, cant give you the scripts because, i don't have info your foreign tables – Ed Bangga Aug 29 '19 at 06:20

DarkRob · Answer 3 · 2019-08-29T06:19:16.407

No you cannot make any column identity after creating from the query.

You have 2 options, either make it from SQL Management Studio or Create another column and copy with identity .

From Management Studio.

Step 1: Select Table design.

Step 2: Change Column properties.

Step 3: Save

Or

You need to create new column with identity.

Create column with identity `Alter table Tablename add newcol int identity(1,1)
Then copy your data from previous column to this column by setting Identity_Insert ON.
Then drop your previous column.
After that change column name by using sp_rename.

Note: But this will change the ordinal position of your column.

ANOTHER OPTION

Create new table with similar structure just make your column identity whichever you want to be.
Copy data from your old table to new table.
Drop old table.
Change name of new table with old table.

Edit:

For case of Foreign Key relationship

If they are not so many and feasible, then you may drop the constraint.

ALTER TABLE Yourtable
DROP FOREIGN KEY FK_PersonOrder;

Then follow the above steps and recreate them at the last.

ALTER TABLE Yourtable
ADD FOREIGN KEY (yourid) REFERENCES Persons(PersonID);

Thank you, "id" i already used as Foreign Key with out deleting if possible — narayana reddy, Aug 29 '19 at 06:11
you choose first option, using `management studio' only, or you may drop the constraint, then follow the steps. — DarkRob, Aug 29 '19 at 06:17

score 0 · Answer 4 · answered Aug 29 '19 at 09:42

Finally i got Solution,

I added new column in 'Cus' table.

alter table Cus add tempCusId int identity;

i removed FK relation in User's Table

and i updated identity values in User Table

update user set id=1 where id= 1;

I Compared Id and TempCusId. After update I removed "Pk" relation in Cus table droped Column "Id",i made "TempCusId" as a "Pk" and identity. Finally User table it self "Id" Column I made FK relation.

And if u have multiple values there than go for a "While" loop

DECLARE @NumberofRowint int=30;

DECLARE @inirow INT=23;
Declare @Grade int ;

WHILE @inirow<= @NumberofRow
BEGIN
DECLARE @ProductID INT=(select Id from [Cus] where id=@inirow)

Set @Grade=(select id from Cus where id=@ProductID)

IF(@Grade= @inirow)
BEGIN
updatetbl_Users set Id=@inirow where id=@ProductID
END

SET @inirow = @inirow + 1;
END;

Alter sql server table Issue

4 Answers4