identity on / off

does anyone know syntext to turn on / off Identity field? I usually do this
via EnterpriseMgr but I want to do this via SQL.
You can't 'turn' it on and off.
You can add a new column, transfer data if appropriate, and remove the old
column.
Enterprise Mangler creates a new table and transfers the data from the old
table to the new table. (One of the many reasons that EM is NOT a good tool
to use to manage your databases.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"David Kwon" <tjk_guard-msnewsgp@.yahoo.com> wrote in message
news:e0MFUC86GHA.1188@.TK2MSFTNGP05.phx.gbl...
> does anyone know syntext to turn on / off Identity field? I usually do
> this
> via EnterpriseMgr but I want to do this via SQL.
>
>
|||Hi,
To add on, you can not switch of the Identity property using a TSQL. But you
could reseed the identity
value using DBCC CHECKIDENT command.While resseding ensure that primary key
violation will
not happen incase the identity column is associated with PKey.
Thanks
Hari
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23yU32r86GHA.4604@.TK2MSFTNGP03.phx.gbl...
> You can't 'turn' it on and off.
> You can add a new column, transfer data if appropriate, and remove the old
> column.
> Enterprise Mangler creates a new table and transfers the data from the old
> table to the new table. (One of the many reasons that EM is NOT a good
> tool to use to manage your databases.)
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> lue "David Kwon" <tjk_guard-msnewsgp@.yahoo.com> wrote in message
> news:e0MFUC86GHA.1188@.TK2MSFTNGP05.phx.gbl...
>
|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23yU32r86GHA.4604@.TK2MSFTNGP03.phx.gbl...
> You can't 'turn' it on and off.
Unless I'm misunderstanding the question, yes you can turn it on and off. I
do it this way:
set identity_insert MyTable on
insert into MyTable
(MyIdentityColumn, MyColumn2, etc... )
values
(Value1, etc... )
set identity_insert MyTable off
--Rob Roberts