Removing identity property

Question

Removing identity property

Mohamed Asane

SSC Veteran

Points: 222
More actions
April 13, 2010 at 2:48 am

#142543

Hi folks,
Is there any way to remove identity property from particular table column(say: TableA column colA).
Note:
1.TableA having more than one million records.
2. Need to remove identity property completely
if you give any assistance regarding this it would be greatly appreciated
Cheers,
Asan

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

joeroshan SSChampion Points: 10377 More actions · Answer 1

http://www.sqlservercentral.com/articles/T-SQL/61979/

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 2

SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

SET IDENTITY_INSERT tablename ON

SET IDENTITY_INSERT tablename OFF

🙂

Mohamed Asane SSC Veteran Points: 222 More actions · Answer 3

thanks joe

Sashi I asked to remove column identity property completely.. however thanks u too..

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Buddy go thro this article

http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/adc78696-9b31-4ffd-b881-5a2f87249cfd

Mohamed Asane SSC Veteran Points: 222 More actions · Answer 5

One more help guys.

Is it possible to specify seed increment dynamically for identity column.

I tried as follow but still getting error. Can u plz hav a look.

create table #t1(ID numeric identity(1,1), Product varchar(10))

go

Insert into #t1 values('DVD')

Insert into #t1 values('Fridge')

Insert into #t1 values('Fan')

go

Declare @MAX_ID numeric

Set @MAX_ID = (SELECT max(ID) from #t1)

Select IDENTITY(Numeric, @MAX_ID, 1) into #t2 from #t1

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

You wil have to use dynamic SQL for this.

Like

if object_id ('tempdb..#t1') is not null

drop table #t1

if object_id ('tempdb..#t2') is not null

drop table #t2

create table #t1(ID numeric identity(1,1), Product varchar(10))

go

Insert into #t1 values('DVD')

Insert into #t1 values('Fridge')

Insert into #t1 values('Fan')

go

Declare @MAX_ID numeric

Set @MAX_ID = (SELECT max(ID) from #t1)

-- only these lines are included

Declare @sql varchar(200)

set @sql = 'Select IDENTITY(Numeric, '+Cast(@MAX_ID as varchar(5))+', 1) as ident into #t2 from #t1'

print @sql

exec(@sql)

But be informed, that #t2 will be visible only inside that dynamic sql.. so will be have to perform all your operations w.r.t to #t2 inside @sql itself...

Cheers..

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 7

I think it should be

Select IDENTITY(Numeric, @MAX_ID, 1) AS RowNum, * into #t2 from #t1

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Mohamed Asane SSC Veteran Points: 222 More actions · Answer 8

thank u very much coldcoffee:-)

hi Kingston Dhasian, Hope you are not compiled this..

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 9

Yes, tried it just now. It doesn't work. I thought the alias name was the problem, hence didn't test. Even i would have done the same in such a case. Learnt something new today:-)

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Mohamed Asane SSC Veteran Points: 222 More actions · Answer 10

Mohamed Asane

SSC Veteran

Points: 222

April 13, 2010 at 3:55 am

#1150577

I agree.. 🙂