I'm using Scriptella for ETL operations, working with many tables referenced with autogenerated ids. I want to re-use these ids without using subqueries, this is my script fragment of the etl file:
<script connection-id="out" if="rownum>1">
SELECT nextval('SQC_CLASE') AS claseId;
INSERT INTO zoologia.clase VALUES( ?claseId, ?phylumId, ?clase, ?subclase, ?infraclase, true );
SELECT nextval('SQC_ORDEN') AS ordenId;
INSERT INTO zoologia.orden VALUES( ?ordenId, ?claseId, ?orden, ?suborden, true );
SELECT nextval('SQC_SUPERFAMILIA') AS superfamiliaId;
INSERT INTO zoologia.superfamilia VALUES( ?superfamiliaId, ?ordenId, ?superfamilia, true );
SELECT nextval('SQC_FAMILIA') AS familiaId;
INSERT INTO zoologia.familia VALUES( ?familiaId, ?superfamiliaId, ?familia, ?subfamilia, ?tribu, true );
SELECT nextval('SQC_GENERO') AS generoId;
INSERT INTO zoologia.genero VALUES( ?generoId, ?familiaId, ?genero, true );
SELECT nextval('SQC_ESPECIE') AS especieId;
INSERT INTO zoologia.especie VALUES( ?especieId, ?generoId, ?especie, ?subespecie, ?variedad, ?genero, true );
</script>
This is obviously wrong because SELECT
can't be performed inside script, right? I'm not really sure how to do it whitout subqueries. I'm using PostgreSQL.
EDIT: What I want to achieve is, for example, get the value of the autogenerated id used in the insertions for the first table, for using it in the insertions of the second table, because the records should be referenced
Thanks to a user from Scriptella forums, this is the solution, a single query with all the values of the sequences:
<query connection-id="external">
<query connection-id="sizoo">
SELECT nextval('SQC_PHYLUM') AS phylumId
, nextval('SQC_CLASE') AS claseId
, nextval('SQC_ORDEN') AS ordenId
, nextval('SQC_SUPERFAMILIA') AS superfamiliaId
, nextval('SQC_FAMILIA') AS familiaId
, nextval('SQC_GENERO') AS generoId
, nextval('SQC_ESPECIE') AS especieId;
<script connection-id="sizoo" if="rownum>1">
INSERT INTO zoologia.phylum VALUES( ?phylumId, ?phylum, true );
INSERT INTO zoologia.clase VALUES( ?claseId, ?phylumId, ?clase, ?subclase, ?infraclase, true );
INSERT INTO zoologia.orden VALUES( ?ordenId, ?claseId, ?orden, ?suborden, true );
INSERT INTO zoologia.superfamilia VALUES( ?superfamiliaId, ?ordenId, ?superfamilia, true );
INSERT INTO zoologia.familia VALUES( ?familiaId, ?superfamiliaId, ?familia, ?subfamilia, ?tribu, true );
INSERT INTO zoologia.genero VALUES( ?generoId, ?familiaId, ?genero, true );
INSERT INTO zoologia.especie VALUES( ?especieId, ?generoId, ?especie, ?subespecie, ?variedad, ?genero, true );
</script>
</query>
</query>