CTE + INSERT INTO + SQLAlchemy

Question

I am trying to insert some date into another table. At first I´ve tried to use sqlalchemy to create such queries, but as I got some error when executing, I tried to solve it through raw SQL, but the error still the same.

I am not very used to CTE commands, so I don´t know if there are some restrinctions over them.

WITH Conv_Pre_Pagos AS 
(SELECT CONVENIO.COD_IDEN, CONVENIO.D_CLIENTE_NOM
FROM db2rpc.CONVENIO 
WHERE CONVENIO.COD_ESPC = 52) 

INSERT INTO DB2I023A.ANL_TARF_PAGAS_PREPAGO (convenio, convenente) SELECT CBR_TARF_REC.NR_DOC_SIS_OGM, Conv_Pre_Pagos.D_CLIENTE_NOM 
FROM DB2TFA.CBR_TARF_REC JOIN Conv_Pre_Pagos ON CBR_TARF_REC.NR_DOC_SIS_OGM = Conv_Pre_Pagos.COD_IDEN

The sentence is bigger, but I removed some data to bring it cleaner. Still, the same error:

ibm_db_dbi::ProgrammingError: SQLNumResultCols failed: [IBM][CLI Driver][DB2] SQL0199N  The use of the reserved word "INSERT" following "INSERT" is not valid.  
   Expected tokens may include:  "(SELECT ,".  SQLSTATE=42601  SQLCODE=-199 

[SQL: WITH Conv_Pre_Pagos AS (SELECT CONVENIO.COD_IDEN, CONVENIO.D_CLIENTE_NOM 
FROM db2rpc.CONVENIO WHERE CONVENIO.COD_ESPC = 52) 

INSERT INTO DB2I023A.ANL_TARF_PAGAS_PREPAGO (convenio, convenente) 
SELECT CBR_TARF_REC.NR_DOC_SIS_OGM, Conv_Pre_Pagos.D_CLIENTE_NOM 
FROM DB2TFA.CBR_TARF_REC JOIN Conv_Pre_Pagos ON CBR_TARF_REC.NR_DOC_SIS_OGM = Conv_Pre_Pagos.COD_IDEN] 

(Background on this error at: https://sqlalche.me/e/14/f405)"

Where does it see an "insert following insert"?

Answer 1

Try this:

INSERT INTO DB2I023A.ANL_TARF_PAGAS_PREPAGO (convenio, convenente)

WITH Conv_Pre_Pagos AS 
(
SELECT CONVENIO.COD_IDEN, CONVENIO.D_CLIENTE_NOM
FROM db2rpc.CONVENIO 
WHERE CONVENIO.COD_ESPC = 52
) 
SELECT CBR_TARF_REC.NR_DOC_SIS_OGM, Conv_Pre_Pagos.D_CLIENTE_NOM 
FROM DB2TFA.CBR_TARF_REC 
JOIN Conv_Pre_Pagos ON CBR_TARF_REC.NR_DOC_SIS_OGM = Conv_Pre_Pagos.COD_IDEN