| Author |
Topic |
|
insanepaul
Posting Yak Master
178 Posts |
 Posted - 2010-10-18 : 07:19:30
|
Hi, I need to switch off the identity for a table. I need to test if it's switched on first. If it is then switch off otherwise ignore.I've googled it and so far this is what I've come up with. Can someone help please? The column is notesubjectidIF EXISTS(SELECT 1 FROM NoteSubject ALTER TABLE NoteSubject set identity_insert NoteSubject off |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-18 : 07:37:53
|
If you want to insert a value into an identity column (which should be an exceptional case) then just do:set identity_insert NoteSubject ON...do your insert...set identity_insert NoteSubject OFF
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2010-10-18 : 07:47:47
|
quote:
Originally posted by webfred
If you want to insert a value into an identity column (which should be an exceptional case) then just do:set identity_insert NoteSubject ON...do your insert...set identity_insert NoteSubject OFF
No, you're never too old to Yak'n'Roll if you're too young to die.
Hi,I've got to switch off the identity for this particular table as many developers are working with the table which has caused problems with mismatched id'e between dev and live. I don't know how to test for the identity and i've got to use the alter table. If you can help I will be much appreciated. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-18 : 07:53:00
|
I think I don't really get you.What do you want to do?Alter a table to change an identity column to "not" identity column?Where then should come the values from for that column on insert?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2010-10-18 : 07:54:33
|
quote:
Originally posted by webfred
I think I don't really get you.What do you want to do?Alter a table to change an identity column to "not" identity column?Where then should come the values from for that column on insert?
No, you're never too old to Yak'n'Roll if you're too young to die.
Developers insert the id for that column instead of that column auto incrementing. I want to keep the column but prevent it from auto incrementing |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-18 : 07:59:00
|
Then the inserts should go like I have already posted.No need to change the table.This is a kind of temp. allowing to insert values for such a column for a certain statement.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2010-10-18 : 08:17:51
|
quote:
Originally posted by webfred
Then the inserts should go like I have already posted.No need to change the table.This is a kind of temp. allowing to insert values for such a column for a certain statement.
No, you're never too old to Yak'n'Roll if you're too young to die.
There's no point having identity on in that case which is why they want to switch it off |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2010-10-18 : 08:40:46
|
quote:
Originally posted by webfred
Then the inserts should go like I have already posted.No need to change the table.This is a kind of temp. allowing to insert values for such a column for a certain statement.
No, you're never too old to Yak'n'Roll if you're too young to die.
I guess then it's not possible to switch it off using code. |
|
|
|
shaggy
Posting Yak Master
248 Posts |
Posted - 2010-10-18 : 11:34:07
|
| add one columnupdate identity column to a new columndrop existing identity columnrename the new column as of dropped identity column --this will permanently drop the identity column if u want the identity column again do the same process as above in reverse |
|
|
|
|
Alter table identity