Convert exiting column into 2nd auto increment column

Question

in Sql Server I have a table StudentMaster in which I have One ID(Primary) column and other "StudentRollNo"(Not Primary) Column . I need to convert StudentRollNo as Another Identity Column .. I Tried this :

ALTER TABLE studentMaster alter column StudentRollNo AS ID + 0

But this is not working 'AS' and '+' are not acceptable in this Query . Is there any other Option in Sql Server ??

You cant have 2 identity keys in a table,I think you want an unique index `ALTER TABLE studentMaster ADD CONSTRAINT Constraint_name UNIQUE (StudentRollNo );` — Mihai, Sep 17 '15 at 12:24
OR you want a sequence http://stackoverflow.com/a/22814247/1745672 — Mihai, Sep 17 '15 at 12:26
this is for new column , I want solution for existing column — Neeraj Verma, Sep 17 '15 at 12:32
@NeerajVerma is you want to same ID value in `StudentRollNo` column — wiretext, Sep 17 '15 at 12:53

score 0 · Accepted Answer · answered Sep 17 '15 at 12:49

0

CREATE SEQUENCE seq START WITH watever_number INCREMENT BY 1;

ALTER TABLE studentMaster ADD CONSTRAINT constraint_name 
      DEFAULT NEXT VALUE FOR seq FOR StudentRollNo

Replace watever_number with the highest value in your column

answered Sep 17 '15 at 12:49

Mihai

26,325
7
66
81

score 0 · Answer 2 · answered Sep 17 '15 at 13:14

0

You can use SQL Computed column on your database table

alter table TableName add ComputedColumnName as Id -- Identity column name

answered Sep 17 '15 at 13:14

Eralper

6,461
2
21
27

Convert exiting column into 2nd auto increment column

2 Answers2