-
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.
-
Urgent: How to drop identity nature of a column using T-sql(Sql server 7). (reply)
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'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'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.
-
Urgent: How to drop identity nature of a column using T-sql(Sql server 7). (reply)
Hi Yariv,
This statment "set identity_insert <table name> OFF" 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 "set identity_insert <table name> OFF".
------------
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'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'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.
-
Urgent: How to drop identity nature of a column using T-sql(Sql server 7). (reply)
Set identity_insert won'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 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it's an identity column */
where id = object_id('yourtable'
and name = 'youridentitycolumn'
go
exec sp_configure 'allow update', 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 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat + 1 /*turn on bit 1 which indicates it's an identity column */
where id = object_id('yourtable'
and name = 'youridentitycolumn'
go
exec sp_configure 'allow update', 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'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.
-
Urgent: How to drop identity nature of a column using T-sql(Sql server 7). (reply)
Hi Ray Rankins
I finished that update work by updating system table "syscolumns" 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'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'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 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it's an identity column */
where id = object_id('yourtable'
and name = 'youridentitycolumn'
go
exec sp_configure 'allow update', 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 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat + 1 /*turn on bit 1 which indicates it's an identity column */
where id = object_id('yourtable'
and name = 'youridentitycolumn'
go
exec sp_configure 'allow update', 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'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.
-
Urgent: How to drop identity nature of a column using T-sql(Sql server 7). (reply)
From what I'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 "set identity_insert" 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'm not exactly sure. I'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 "syscolumns" 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'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'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 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it's an identity column */
where id = object_id('yourtable'
and name = 'youridentitycolumn'
go
exec sp_configure 'allow update', 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 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat + 1 /*turn on bit 1 which indicates it's an identity column */
where id = object_id('yourtable'
and name = 'youridentitycolumn'
go
exec sp_configure 'allow update', 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'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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|