Set auto increment on existing column

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

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

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

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

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

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

Thank you Johnbhatt.
But i want sql query..

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

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

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

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