Re: Query about inserting records

"Steven H." schrieb:
>
> In my SELECT statement, I need to insert records from one table into
> another, but also insert a unique value into an additional field in
> the target table for each record inserted. For example,
>
> Inserted records, target table:-
>
> Field_1 Field_2 Field_3
> bill smith 1
> ted wilson 2
>
> Field_1 and Field_2 come from the source table. Field_3 I want to
> generate a unique numeric value for each record, starting from 1.
>
> Is this possible?
>

Hi,

if you don't need a continious numbering use sequneces and triggers:

Drop Sequence SEQ_AuszahlungsGrund_ID ; Create Sequence SEQ_AuszahlungsGrund_ID ;

create or replace trigger tI_AuszahlungsGrund   BEFORE INSERT
  on AuszahlungsGrund
  for each row
begin

        SELECT SEQ_AuszahlungsGrund_ID.NEXTVAL INTO :new.field3 FROM dual ; end;

Since sequences are cached in the SGA each database shutdown and restart will eat up some numbers. If you're in need of a continuous numbering try:

create or replace trigger tI_AuszahlungsGrund   BEFORE INSERT
  on AuszahlungsGrund
  for each row
new_number number;
begin

	SELECT
		MAX(field3) 
	INTO
		new_number
	FROM
		target_table
	WHERE
		rowid != :new.rowid	-- should prevent from 'TABLE IS MUTATING'- eror
	;	
	:new.field3=new_number+1 ;

HTH Matthias
--
grema_at_t-online.de

Es gibt nichts Neues mehr.
Alles, was man erfinden kann, ist schon erfunden worden. Charles H. Duell, Leiter des US Patentamtes bei seinem Rücktritt 1899 Received on Tue Feb 16 1999 - 04:57:58 CST