Identity_Insert is set to OFF

Hi,
I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
to Duplicate a record from a form and the linked records in its subform.
Parent form duplicate goes to table "Jobs" where JobID is the key, records
from subform with (Link JobID) go to table "Samples". I've created an update
query in order to do this and I'm getting the following error-message:
Cannot insert explicit value for identity column in table "Jobs" when
IDENTITY_INSERT is set to OFF
Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
other way to duplicate these records?
Thanks in advance
gaba
Sorry,
I've meant "append query" not update
gaba
"gaba" wrote:

> Hi,
> I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
> to Duplicate a record from a form and the linked records in its subform.
> Parent form duplicate goes to table "Jobs" where JobID is the key, records
> from subform with (Link JobID) go to table "Samples". I've created an update
> query in order to do this and I'm getting the following error-message:
> Cannot insert explicit value for identity column in table "Jobs" when
> IDENTITY_INSERT is set to OFF
> Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
> other way to duplicate these records?
> Thanks in advance
>
> --
> gaba
|||gaba wrote:
> Hi,
> I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
> to Duplicate a record from a form and the linked records in its subform.
> Parent form duplicate goes to table "Jobs" where JobID is the key, records
> from subform with (Link JobID) go to table "Samples". I've created an update
> query in order to do this and I'm getting the following error-message:
> Cannot insert explicit value for identity column in table "Jobs" when
> IDENTITY_INSERT is set to OFF
> Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
> other way to duplicate these records?
>
Hi gaba,
The clue is in the question :-)
In order to insert data into a table which has an identity column, if
you want to insert a value instead of accepting the default, do the
following:
SET IDENTITY_INSERT <table name> ON
then doing your inserts, and then
SET IDENTITY_INSERT <table name> OFF
Rather irritatingly, you can only set this option against one table at
a time.
|||Thanks Damien. That was it. So simple...
gaba
"Damien" wrote:

> gaba wrote:
> Hi gaba,
> The clue is in the question :-)
> In order to insert data into a table which has an identity column, if
> you want to insert a value instead of accepting the default, do the
> following:
> SET IDENTITY_INSERT <table name> ON
> then doing your inserts, and then
> SET IDENTITY_INSERT <table name> OFF
> Rather irritatingly, you can only set this option against one table at
> a time.
>