Hi.

The scenario is this_

insert into aTable
select * from aTable

aTable has  an  automatically generated identity field. The statement fails with key
violation error.

Tried

insert into aTable
select
  null as KeyField,
  ...
from aTable


But null is not allowed in select.

Any idea, how to make this statement  executable?


Regards,
Leslie
 

Leslie


Use a list of fields and exclude the autoinc one.

Roy Lambert [Team Elevate]

Roy,

Wow that was quick! )

As far as I understand "Insert into Seelct ... " expects the same number of fields in the
same order in the source as they exists in the target table. How can I skip any field?

Leslie
  

I think I got it now: I need a list with the names of the target fields  too, right?
  

Roy,

OK, I have excluded the field with the primary key, but still getting the same error. The
value assigned to the keyfield for the inserted records starts with  1 even after setting
the seed value to max(KeyField) + 1.

Any idea what can be wrong/ should be checked  here?

Leslie

  

Leslie

Something like


insert  into emarchive (_fkMailBoxes,_MsgID) select _fkMailBoxes,_MsgID from emails where _fkmailboxes = 5

ie just leave out the autoinc field - works here


Roy Lambert [Team Elevate]

Roy,

This will probably require Tim's help.

I made a workaround by implementing  a key generation which is called  from the Before
insert trigger. No problem there any more. The next problem is that the source selection
has 10 records (checked it separately), but only the last one is actually inserted. The
key generator is  increased  by the number of the records, which indicates, that all
records are processed, but only the last one makes it into the target dataset. I cannot
see anything in my code which could cause this.

This is the  statement I  am using for testing:

insert into state
   (  Parent_ID,
      State,
       ...
   )
    select
      149 as Parent_ID,
      State,
       ...
    from State
      where Parent_ID = 30;  


Regards,
Leslie

Tim,

In the conext of a transaction the statement  in the previous comment works as expected. I
am short on time, so  a working solution is all I need at the moment. But there are two
potential bugs you may want check out.

1. The seed value was not used in the generated value in my first attempt.
2. Only the last record was inserted without the use of a transaction. (In this case the
stored proc generating the value started and commited a transaction. )

Regards,
Leslie

Leslie,

<< aTable has  an  automatically generated identity field. The statement
fails with key violation error. >>

If you defined the IDENTITY column as GENERATED BY DEFAULT AS, then this is
the correct result.  If you always want the IDENTITY column to assign a new
value, then use GENERATED ALWAYS AS.

<< insert into aTable
select
  null as KeyField,
  ...
from aTable >>

Needs to be:

insert into aTable
select
  CAST(null AS INTEGER) as KeyField,
  ...
from aTable

You have to give the SELECT a type so that it can do the proper type
checking at compilation time.

--
Tim Young
Elevate Software
www.elevatesoft.com

Leslie,

<< 1. The seed value was not used in the generated value in my first
attempt. >>

The seed value has no bearing on this (see my message about BY DEFAULT vs.
ALWAYS).

<< 2. Only the last record was inserted without the use of a transaction.
(In this case the stored proc generating the value started and commited a
transaction. ) >>

I can't comment on this without seeing the actual trigger code.

--
Tim Young
Elevate Software
www.elevatesoft.com