identity insert issue

26 Nov 2007 10:37 PM

sharman

I am trying to update an identity column with a new value. I am doing the
following:

set identity_insert tbgfmla4.dbo.[tblname] on
go
update [tblname]
set identitycolumn= 124926
where [fieldname] = 'ABCD'
and it gives me an error

Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'identitycolumn'.

What am I doing wrong? Thanks in advance.

26 Nov 2007 10:48 PM

David Portas

Show quote

"sharman" <shar***@discussions.microsoft.com> wrote in message
news:8E0F1ECE-30E1-4780-B5A0-E89BCBE8ABF9@microsoft.com...
>I am trying to update an identity column with a new value. I am doing the
> following:
>
> set identity_insert tbgfmla4.dbo.[tblname] on
> go
> update [tblname]
> set identitycolumn= 124926
> where [fieldname] = 'ABCD'
> and it gives me an error
>
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'identitycolumn'.
>
> What am I doing wrong? Thanks in advance.

An IDENTITY column cannot be updated under any circumstances, irrespective
of the IDENTITY_INSERT setting. If this is a problem for you then don't use
IDENTITY.

You can however DELETE and the re-INSERT the IDENTITY value if
IDENTITY_INSERT is on, assuming you avoid violating any constraints by doing
so.

--
David Portas

27 Nov 2007 2:05 PM

Madhivanan

On Nov 27, 3:37 am, sharman <shar***@discussions.microsoft.com> wrote:
Show quote

> I am trying to update an identity column with a new value. I am doing the
> following:
>
> set identity_insert tbgfmla4.dbo.[tblname] on
> go
> update [tblname]
> set identitycolumn= 124926
> where [fieldname] = 'ABCD'
> and it gives me an error
>
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'identitycolumn'.
>
> What am I doing wrong? Thanks in advance.

Note that with set identity_insert tbgfmla4.dbo.[tblname] on, you can
only add value to the column and you cant update it
Why do you want to update identity column?