[solved: 3 ways of] insert select from - within the SAME table

[Nicole]

I use Firebird 4 and want to do this:
- copy one existing dataset, no matter if with * or listed items
- make my generator make an new id within the same table
- copy the values into it

I tried it all, with all brackets settings.
The error message jumps just before the bracket and claims "invalid token".

How to make it work?
If possible I prefer select *, but there was a bug with it in a previous Firebird version (2.5.)

This version below does not work.
The idea was: select everything except the id = id_einkommen and make Firebird generate a new id with inserting the values of the old one, e.g. 10.

Code: MySQL  [Select][+][-]

1. INSERT INTO TBEINKOMMEN
2. select
3. ((
4. SELECT a.TYP, a.ISIN, a.WKZ, a.BEZEICHNUNG, a.EINTRAGSDATUM,
5.     a.EMITTENT, a.EMISSIONSDATUM, a.AGIO, a.RENDITE_P_A, a.EMISSIONSPREIS,
6.     a.LAND, a.BRANCHE, a.EMISSIONSVOLUMEN, a.ZINSBERECHNUNG, a.BESONDERHEITEN,
7.     a.KUPONTEXT, a.NOMINALZINSSATZ, a.MODIFIED_DURATION, a.RATING, a.WAEHRUNG,
8.     a.ZUHANDELNAN, a.MATURITY, a.KLEINSTEEINHEIT, a.ERSTERKUPON, a.LETZTERKUPON,
9.     a.EMISSIONSKURS_IN_PROZENT, a.TILGUNGSKURS_IN_PROZENT,
10.     a.KAUFKURS_IN_PROZENT, a.STEUER_IN_PROZENT, a.STEUER_ALS_BETRAG,
11.     a.KUPONS_IM_JAHR, a.KUPON_ALS_BETRAG, a.DURATION, a.BEZEICHNUNG_LANG,
12.     a.BETRAG_INVESTIERT, a.KURS, a.SPESEN, a.DEPOT, a.MARKETMAKER,
13.     a.KAUFPREIS_SUMME )
14.     FROM TBEINKOMMEN a
15. WHERE
16.     a.ID_EINKOMMEN = '10');

[Nicole]

my workaround was this, I added a RETURNING

Code: MySQL  [Select][+][-]

1. INSERT INTO TBEINKOMMEN (TYP)
2.        select TYP from tbeinkommen where id_einkommen = 2
3. RETURNING id_einkommen;

[egsuh]

If you are copying all the fields then you don't have to use any  brackets.

Code: SQL  [Select][+][-]

1. INSERT INTO TBEINKOMMEN SELECT * FROM TBEINKOMMEN WHERE ID_EINKOMMEN = '10';
2.  

But if the ID must be different, you may use trigger -- before insert. Set new.ID_einkommen to new id there.

Or,

Code: SQL  [Select][+][-]

1. INSERT INTO TBEINKOMMEN
2.     SELECT  NEXT VALUE FOR ID_Generator,
3.              TYP, ISIN, WKZ, BEZEICHNUNG, ... -- other fields
4.    FROM TBEINKOMMEN
5.    WHERE  ID_EINKOMMEN = '10');

assuming that ID_EINKOMMEN is the first field. This works in Firebird.  But your field ID_EinKommen is string type, then I have no idea how you would create new id. You may use stored procedure to create a new id.

[Zvoni]

I use Firebird 4 and want to do this:
- copy one existing dataset, no matter if with * or listed items
- make my generator make an new id within the same table
- copy the values into it

I tried it all, with all brackets settings.
The error message jumps just before the bracket and claims "invalid token".

How to make it work?
If possible I prefer select *, but there was a bug with it in a previous Firebird version (2.5.)

This version below does not work.
The idea was: select everything except the id = id_einkommen and make Firebird generate a new id with inserting the values of the old one, e.g. 10.

Code: MySQL  [Select][+][-]

1. INSERT INTO TBEINKOMMEN
2. select
3. ((
4. SELECT a.TYP, a.ISIN, a.WKZ, a.BEZEICHNUNG, a.EINTRAGSDATUM,
5.     a.EMITTENT, a.EMISSIONSDATUM, a.AGIO, a.RENDITE_P_A, a.EMISSIONSPREIS,
6.     a.LAND, a.BRANCHE, a.EMISSIONSVOLUMEN, a.ZINSBERECHNUNG, a.BESONDERHEITEN,
7.     a.KUPONTEXT, a.NOMINALZINSSATZ, a.MODIFIED_DURATION, a.RATING, a.WAEHRUNG,
8.     a.ZUHANDELNAN, a.MATURITY, a.KLEINSTEEINHEIT, a.ERSTERKUPON, a.LETZTERKUPON,
9.     a.EMISSIONSKURS_IN_PROZENT, a.TILGUNGSKURS_IN_PROZENT,
10.     a.KAUFKURS_IN_PROZENT, a.STEUER_IN_PROZENT, a.STEUER_ALS_BETRAG,
11.     a.KUPONS_IM_JAHR, a.KUPON_ALS_BETRAG, a.DURATION, a.BEZEICHNUNG_LANG,
12.     a.BETRAG_INVESTIERT, a.KURS, a.SPESEN, a.DEPOT, a.MARKETMAKER,
13.     a.KAUFPREIS_SUMME )
14.     FROM TBEINKOMMEN a
15. WHERE
16.     a.ID_EINKOMMEN = '10');

This wouldn't work in any way. (Besides the wrongly placed brackets)
You're ommitting the Field "id_einkommen" in your Select, so your INSERT tries to Insert Field TYP into id_einkommen (if id_einkommen is the first field).

Bottom Line: If you're ommiting fields from the Select you have to explicitly state the Columns in the INSERT, and those columns must be in the same order as the fields in the Select.
egsuh alluded to this in his example

Aircode

Code: SQL  [Select][+][-]

1. INSERT INTO tbeinkommen (Typ,Isin,WKZ, etc......)
2. SELECT a.Typ, a.Isin etc...
3. FROM tbeinkommen
4. WHERE id_einkommen='10';  -- Is this really Text?!?!?!
5.  

[rvk]

But if the ID must be different, you may use trigger -- before insert. Set new.ID_einkommen to new id there.

Normally you could just pass NULL for ID. The trigger should handle creating a new ID in that case. (No need to use "NEXT VALUE")

So something like this should work too:

Code: SQL  [Select][+][-]

1. INSERT INTO TBEINKOMMEN
2. SELECT NULL, a.TYP, a.ISIN, a.WKZ, a.BEZEICHNUNG, a.EINTRAGSDATUM,
3.     a.EMITTENT, a.EMISSIONSDATUM....

(with or without RETURNING depending on your needs)