April 16, 2004 at 6:11 am
Folks,
Is it possible to turn the IDENTITY characteristic of an identity field on and off using ALTER TABLE and ALTER COLUMN?
I would like do this to a bunch of columns on various tables created with SELECT...INTO queries. I want to "turn off" the identity characteristic, update the values, and then turn it on again.
Alternatively, I know I can drop the column, and then make a new one -- but I would like to create the new one in the same ordinal position as the old one. Is that possible?
What I want, with the minumum fuss and simplest possible coding, is to be able to take any table with an arbitrary field structure, turn off its identity column, change that field's values, and turn it on again, without changing its column order. I won't know the structure of the table before hand -- so enumerating the columns for SELECT...INTO and INSERT ... SELECT... statements would be quite painful to code, and is a route I would like to avoid.
Am I out of luck, dear friends? 😉
- Tom
April 16, 2004 at 4:20 pm
Thanks stacenic,
I thought of that, too, but it doesn't solve my problem. I don't want to INSERT new rows into the table, I want to change existing values.
Turning SET IDENTITY_INSERT to OFF doesn't not allow you to update the existing values. It only allows you to control what new values will be for newly inserted rows.
- Tom
April 16, 2004 at 4:49 pm
Try:
ALTER TABLE tablename
NOCHECK CONSTRAINT constraint_name
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply