1.批量插入sql语法
insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual
或者
INSERT ALL INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3') INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3') INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3') . . . SELECT 1 FROM DUAL;
基于mybatis时写法如下:
<insert id="insert" useGeneratedKeys="false"> INSERT INTO ${mode}.PRACTICE_STORE(MEM_GUID,INS_DT,STATUS) <foreach collection="set" separator="UNION ALL" item="memGuid"> SELECT #{memGuid},sysdate,'1' FROM dual </foreach> </insert>
注意:useGeneratedKeys必须设置为false 否则报语法错误。如下:<insert id="insert" useGeneratedKeys="false"> INSERT ALL <foreach collection="set" item="memGuid"> INTO ${mode}.PRACTICE_STORE(MEM_GUID,INS_DT,STATUS) VALUES (#{memGuid},sysdate,'1') </foreach> SELECT 1 FROM dual </insert>
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: INSERT ALL INTO UCORD.PRACTICE_STORE(MEM_GUID,INS_DT,STATUS) VALUES (?,sysdate,'1') INTO UCORD.PRACTICE_STORE(MEM_GUID,INS_DT,STATUS) VALUES (?,sysdate,'1') SELECT 1 FROM dual
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束