Urgent: How to drop identity nature of a column using T-sql(Sql server 7).

I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn't able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.

execute the following prior to updating the table with the identity column:

set identity_insert <table name> OFF

Make sure you turn it ON after you&#39;re done.


------------
Gopal at 12/19/00 12:41:11 PM

I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn&#39;t able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.

Hi Yariv,
This statment &#34;set identity_insert <table name> OFF&#34; helps to insert a new records into a table in the place of already deleted records. For example, four records in table say 1,2,3 and 4. suppose 3 is deleted. then we can insert a new record in this gap(ie. with same value 3) using this &#34;set identity_insert <table name> OFF&#34;.

------------
Yariv at 12/19/00 12:49:51 PM

execute the following prior to updating the table with the identity column:

set identity_insert <table name> OFF

Make sure you turn it ON after you&#39;re done.


------------
Gopal at 12/19/00 12:41:11 PM

I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn&#39;t able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.

Set identity_insert won&#39;t work. Neither can you use alter table to turn off the identity property. The only way I know of to turn off the identity property is to modify the system tables. Try this:

sp_configure &#39;allow update&#39;, 1
go
reconfigure with override
go
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it&#39;s an identity column */
where id = object_id(&#39;yourtable&#39;)
and name = &#39;youridentitycolumn&#39;
go
exec sp_configure &#39;allow update&#39;, 0
go
reconfigure with override
go


Put in yourtablename and youridentitycolumn names. Once this runs, do your update on the column, and then set the identity property back on:


sp_configure &#39;allow update&#39;, 1
go
reconfigure with override
go
update syscolumns set colstat = colstat + 1 /*turn on bit 1 which indicates it&#39;s an identity column */
where id = object_id(&#39;yourtable&#39;)
and name = &#39;youridentitycolumn&#39;
go
exec sp_configure &#39;allow update&#39;, 0
go
reconfigure with override
go


-Ray Rankins


------------
Gopal at 12/19/00 12:41:11 PM

I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn&#39;t able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.

Hi Ray Rankins
I finished that update work by updating system table &#34;syscolumns&#34; and i thought of entering solution for this. I received ur solution for this issue with clean explanation. This will help others to update identity column using these steps. But i&#39;m on the way to find out how to bring back identity nature of that field with custom seeds and increment(We can bring back identity nature with default seed=1 and incr=1 by setting colstat=1). If you know about this then let us know. Thanks for your reply.

Regards
Gopal
------------
Ray Rankins at 12/19/00 4:10:56 PM

Set identity_insert won&#39;t work. Neither can you use alter table to turn off the identity property. The only way I know of to turn off the identity property is to modify the system tables. Try this:

sp_configure &#39;allow update&#39;, 1
go
reconfigure with override
go
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it&#39;s an identity column */
where id = object_id(&#39;yourtable&#39;)
and name = &#39;youridentitycolumn&#39;
go
exec sp_configure &#39;allow update&#39;, 0
go
reconfigure with override
go


Put in yourtablename and youridentitycolumn names. Once this runs, do your update on the column, and then set the identity property back on:


sp_configure &#39;allow update&#39;, 1
go
reconfigure with override
go
update syscolumns set colstat = colstat + 1 /*turn on bit 1 which indicates it&#39;s an identity column */
where id = object_id(&#39;yourtable&#39;)
and name = &#39;youridentitycolumn&#39;
go
exec sp_configure &#39;allow update&#39;, 0
go
reconfigure with override
go


-Ray Rankins


------------
Gopal at 12/19/00 12:41:11 PM

I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn&#39;t able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.

From what I&#39;ve seen, it does retain whatever seed and increment it had before setting colstat to 0 and back to 1. If you want to CHANGE the seed and increment after performing the update, you can change the seed by inserting a new row after re-enabling the identity property on the column using the &#34;set identity_insert&#34; command and inserting a new row with a value higher than the max identity value in the table. As for changing the increment, it looks like the original seed and increment may be stored in a binary field in syscolumns called autoval. How exactly the data is stored in there, I&#39;m not exactly sure. I&#39;d have to play around with it some on a test system to figure it out and how to update it.

-Ray


------------
gopal at 12/19/00 4:25:18 PM

Hi Ray Rankins
I finished that update work by updating system table &#34;syscolumns&#34; and i thought of entering solution for this. I received ur solution for this issue with clean explanation. This will help others to update identity column using these steps. But i&#39;m on the way to find out how to bring back identity nature of that field with custom seeds and increment(We can bring back identity nature with default seed=1 and incr=1 by setting colstat=1). If you know about this then let us know. Thanks for your reply.

Regards
Gopal
------------
Ray Rankins at 12/19/00 4:10:56 PM

Set identity_insert won&#39;t work. Neither can you use alter table to turn off the identity property. The only way I know of to turn off the identity property is to modify the system tables. Try this:

sp_configure &#39;allow update&#39;, 1
go
reconfigure with override
go
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it&#39;s an identity column */
where id = object_id(&#39;yourtable&#39;)
and name = &#39;youridentitycolumn&#39;
go
exec sp_configure &#39;allow update&#39;, 0
go
reconfigure with override
go


Put in yourtablename and youridentitycolumn names. Once this runs, do your update on the column, and then set the identity property back on:


sp_configure &#39;allow update&#39;, 1
go
reconfigure with override
go
update syscolumns set colstat = colstat + 1 /*turn on bit 1 which indicates it&#39;s an identity column */
where id = object_id(&#39;yourtable&#39;)
and name = &#39;youridentitycolumn&#39;
go
exec sp_configure &#39;allow update&#39;, 0
go
reconfigure with override
go


-Ray Rankins


------------
Gopal at 12/19/00 12:41:11 PM

I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn&#39;t able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.