Set auto increment on existing column

Posted by Muhsinathk under Sql Server on 6/21/2012 | Points: 10 | Views : 26875 | Status : [Member] | Replies : 9
Hi,
I have a table Student with columns (StudID,StudName,Class).
StudID is the primary key of this table.I want to add identity(101,1) on this table.
Is there any solution for this?




Responses

Posted by: Gow.Net on: 6/21/2012 [Member] Starter | Points: 25

Up
0
Down
ya it possible
refer this link:http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss

gowthaman8870226416

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/21/2012 [Member] Bronze | Points: 25

Up
0
Down
Thank you..
But i want ALTER query for adding auto increment on existing table.

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 6/21/2012 [Member] Starter | Points: 25

Up
0
Down
U can alter the table

Alter Table emp(empid int identity(1,1),empname varchar(90),empaddress varchar(90))


Hope this will help

Regards

Sriram.R

sriram

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/21/2012 [Member] Bronze | Points: 25

Up
0
Down
Thank you @Sriramnandha
This query also didn't work correctly..

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Johnbhatt on: 6/21/2012 [Member] Starter | Points: 25

Up
0
Down
Open SSMS (SQL Server Management Studio) and Login with Server Administrator (sa login). Then Expand your Databases, choose Table and Right Click on Table and Choose Design. You will see a screen, where you have Properties, Click on StudID column which is your PK also, then go to Properties in Splitted Screen and Click on Identity Specification and Change it to Yes, Set Identity to number required ex. 1, 10 or 100 as per your requirement.

Then Save by Pressing Ctrl+S. Have a look at attached Image.
 Download source file

John Bhatt
Glad to Know, Free to Share.....
http://www.johnbhatt.com

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/22/2012 [Member] Bronze | Points: 25

Up
0
Down
Thank you Johnbhatt.
But i want sql query..

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeraaj.Sharma on: 6/4/2013 [Member] Starter | Points: 25

Up
0
Down

make sure database is offline when using on production server or no transaction occurring on table

-- old table with data
CREATE TABLE dbo.Tbl
(
Id int NOT NULL,
Name varchar(50) NULL
)

insert into dbo.Tbl
select 1 , 'name1' union
select 2 , 'name2' union
select 3 , 'name2'



create a new table with identity column


CREATE TABLE dbo.TblDuplicate
(
Id int NOT NULL
IDENTITY(1, 1),
Name varchar(50) NULL
)


SET IDENTITY_INSERT dbo.TblDuplicate ON
insert into dbo.TblDuplicate (ID,name )
Select * From dbo.Tbl
SET IDENTITY_INSERT dbo.TblDuplicate OFF


-- dropping old table
DROP TABLE dbo.Tbl
go

Exec sp_rename 'TblDuplicate', 'Tbl'

select * From dbo.Tbl


exec sp_help 'Tbl'



visit my blog : http://tutorialsqlserver.com/




Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Peermohamedmydeen on: 6/4/2013 [Member] Bronze | Points: 25

Up
0
Down
HI,

Find out the max of the column value and use the DBCC CHECKIDENT (ColumnName,RESEED, max+1) command.

Thanks
Mydeen



Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Peermohamedmydeen on: 6/4/2013 [Member] Bronze | Points: 25

Up
0
Down
create table stud(StudId int,StudName NVARCHAR(50))


Insert into stud(StudId,StudName)
Select 1,'A'

Insert into stud(StudId,StudName)
Select 3,'B'

Insert into stud(StudId,StudName)
Select 5,'C'

Insert into stud(StudId,StudName)
Select 7,'D'

Declare @MaxSeed Int

Select @MaxSeed = max(StudId)+1 from Stud


Select @MaxSeed


Alter table stud add NewStudId int identity(1,1)

--Write a logic to insert all the values of old column to new column and drop the old column

--Rename the new column to cold column

DBCC CHECKIDENT (stud,RESEED,7)

Insert into stud(StudName)
Select 'E'

Select * from stud

Regards
Mydeen

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response