Inserting records while incrementing an ID field.

  • Thread starter Thread starter jeff
  • Start date Start date
J

jeff

Hi gurus!

I am wanting to INSERT mulitple records into a table based on a SELECT
statement from the same table.
I have an ID field in the Table I am APPENDing records to which needs to be
incremented on each record added based on the last MAX(ID)+1
eg

INSERT INTO TAG_CONVENTION ( TAG_CONV_ID, SEQ_NUM, AREA_ID, UNIT_ID,
TAG_CONV_TYPE_ID )
SELECT DISTINCT
(SELECT MAX (TAG_CONV_ID) +1 FROM TAG_CONVENTION) AS TAG_CONV_ID,
(SELECT MAX (SEQ.SEQ_NUM) +1 FROM TAG_CONVENTION AS SEQ
WHERE ((SEQ.TAG_CONV_TYPE_ID=2) AND (SEQ.UNIT_ID=UNIT.UNIT_ID))) AS
SEQ_NUM,
UNIT.AREA_ID, UNIT.UNIT_ID, 2 AS TAG_CONV_TYPE_ID
FROM TAG_CONVENTION AS UNIT
WHERE (UNIT.UNIT_ID<>0);

The SELECT MAX (SEQ.SEQ_NUM) +1 works correctly because it is based on each
UNIT record existing BUT
the SELECT MAX (TAG_CONV_ID) +1 returns the same value for ALL records
INSERTed.

Can anyone help me with this please??

Cheers
Jeff
 
Hi gurus!

I am wanting to INSERT mulitple records into a table based on a SELECT
statement from the same table.
I have an ID field in the Table I am APPENDing records to which needs to be
incremented on each record added based on the last MAX(ID)+1
eg

INSERT INTO TAG_CONVENTION ( TAG_CONV_ID, SEQ_NUM, AREA_ID, UNIT_ID,
TAG_CONV_TYPE_ID )
SELECT DISTINCT
(SELECT MAX (TAG_CONV_ID) +1 FROM TAG_CONVENTION) AS TAG_CONV_ID,
(SELECT MAX (SEQ.SEQ_NUM) +1 FROM TAG_CONVENTION AS SEQ
WHERE ((SEQ.TAG_CONV_TYPE_ID=2) AND (SEQ.UNIT_ID=UNIT.UNIT_ID))) AS
SEQ_NUM,
UNIT.AREA_ID, UNIT.UNIT_ID, 2 AS TAG_CONV_TYPE_ID
FROM TAG_CONVENTION AS UNIT
WHERE (UNIT.UNIT_ID<>0);

The SELECT MAX (SEQ.SEQ_NUM) +1 works correctly because it is based on each
UNIT record existing BUT
the SELECT MAX (TAG_CONV_ID) +1 returns the same value for ALL records
INSERTed.
Click to expand...

You may need to use a handy little auxiliary table (which has many uses); I
call mine Num, with one Long Integer field N, prefilled with values from 0
through 10000 or so.

You can then include Num in your append query with no join line, but a
criterion to limit the number of values of N to the number of records you want
to add.
 
Back
Top