How to set Identity in sql

To get your question right...

You have identity set for sno but you want to set identify for ID column. Is that right?

Best Regards,

VG
www.TeacherJi.com

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

Hi Gsolvers


yes i set identity sno . . but i want to remove sno identity and set id column as identity by query . . any suggestion , ,

Best Regard's
Prabhakar

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

See the below query if that helps

CREATE TABLE IdentityTable ( ID int , Name varchar(10) )

insert into IdentityTable values (5,'abc')
insert into IdentityTable values (6,'deb')
insert into IdentityTable values (7,'23423')


ALTER TABLE IdentityTable ADD IID INT IDENTITY(1,1)

select * from IdentityTable

set identity_insert IdentityTable on
insert into IdentityTable (IID ,Name ) values (5,'abc')
insert into IdentityTable (IID ,Name ) values (6,'deb')
insert into IdentityTable (IID ,Name ) values (7,'23423')
set identity_insert IdentityTable off


select * from IdentityTable
delete from IdentityTable where ID is not null

DBCC CHECKIDENT ('IdentityTable', RESEED, 8);

Best Regards,

VG
www.TeacherJi.com

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

Check these links

http://www.bennadel.com/blog/24-Turning-Off-and-On-Identity-Column-in-SQL-Server.htm

http://geekswithblogs.net/TakeNote/archive/2007/11/30/117258.aspx

Thanks
Karthik
www.f5Debug.net

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

hello . . Gsolvers & Karthikanbarasan


i am don't want to set

SET IDENTITY_INSERT tablename ON
SET IDENTITY_INSERT tablename OFF
and also not set RESEED . . i want to remove sno identity then set id column Identity . . by query . .

Best Regard's
Prabhakar

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

It's not straight forward but it is possible (hope this not a partitioned table)

There are few options
- If the table is very big and is not petitioned you can use the switch feature in partitioning
- If the table is small create table with new structure and copy the data to the new table and rename the table

Best Regards,

VG
www.TeacherJi.com

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

ok guy's . .

now i find the solution . .
firstly drop column the set Identity . .


Alter Table tbl Drop column [sno]
Alter Table tbl Add id int identity(1,1)

Best Regard's
Prabhakar

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

ok good!!!

Thanks
Karthik
www.f5Debug.net

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

If you want to retain the data for that particular column. you can use this method.

CREATE TABLE dbo.TEMPEMPLOYEE--New table creation with identity
(
ID INT NOT NULL IDENTITY (1, 1),
EMPNAME varchar (25),
AGE INT
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.TEMPEMPLOYEE ON
GO

IF EXISTS(SELECT * FROM dbo.EMPLOYEE)

INSERT INTO dbo.TEMPEMPLOYEE (ID, EMPNAME, AGE)--Insert the values from the original table to the newly created table

SELECT ID, EMPNAME, AGE FROM dbo.EMPLOYEE TABLOCKX

GO
SET IDENTITY_INSERT dbo.TEMPEMPLOYEE OFF
GO
DROP TABLE dbo.EMPLOYEE--Remove the old table
GO
EXEC sp_rename 'TEMPEMPLOYEE', 'EMPLOYEE'--Rename the newly created table name as the original table name

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

Hi,

Refer this links,

http://gala4th.blogspot.com/2009/01/turning-off-and-on-identity-column-in.html

http://www.simple-talk.com/sql/t-sql-programming/identity-columns/

Regards,
Susan

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

alter table table_name add colname datatype identity(1,1)

Thanks&Regards
LakshmiNarayana Nalluri.

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