DB2中带CTE的INSERT语句报SQL0199N，请求排查报错原因

尝试将数据插入另一张表，最初用SQLAlchemy构建查询执行出错，改用原生SQL后错误依旧。不太熟悉CTE命令，怀疑存在使用限制。

使用的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

报错信息：

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] 

疑问：报错提示中所谓的“INSERT后紧跟INSERT”是从哪里识别到的？

这个报错是DB2语法解析时的误报，实际问题是你使用CTE的语法不符合DB2的要求。

DB2不支持将WITH子句作为独立语句前缀直接跟INSERT，它要求WITH子句必须依附于后续的SELECT语句。正确的写法是把CTE放在INSERT的SELECT部分之前，调整后的SQL如下：

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

原写法中，DB2解析器看到WITH子句结束后直接出现INSERT，而它默认WITH之后应该跟SELECT这类查询语句，因此出现逻辑混乱，错误判定为“INSERT后紧跟INSERT”，本质是语法结构不符合DB2规范。

内容的提问来源于stack exchange，提问作者FábioRB