How to set Identity in sql

Posted by Prabhakar under Sql Server on 4/11/2011 | Points: 10 | Views : 6201 | Status : [Member] [MVP] | Replies : 11
Hi firends

I want to set identity by query . . in my table column sno i am set all ready set identity to yes but i want to set id column as a identity in a query how'z the possible suggest me .

Best Regard's
Prabhakar



Responses

Posted by: Gsolvers on: 4/11/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Prabhakar on: 4/11/2011 [Member] [MVP] Starter | Points: 25

Up
0
Down
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

Posted by: Gsolvers on: 4/11/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Karthikanbarasan on: 4/11/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25
Posted by: Prabhakar on: 4/11/2011 [Member] [MVP] Starter | Points: 25

Up
0
Down
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

Posted by: Gsolvers on: 4/11/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Prabhakar on: 4/11/2011 [Member] [MVP] Starter | Points: 25

Up
0
Down
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

Posted by: Karthikanbarasan on: 4/11/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
ok good!!!

Thanks
Karthik
www.f5Debug.net

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

Posted by: Radhikalingam on: 4/12/2011 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Susanthampy on: 5/24/2011 [Member] [MVP] Bronze | Points: 25
Posted by: Lakn2 on: 5/25/2011 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response