前言
总感觉自己做过批量插入,又好像没有,刻意看一下,我一次插入了1W条数据,结合自己的MYSQL版本最大插入来做,记得做好字段阈值
MYSQL 批量
XML插入
<insert id="insertAll" parameterType="java.util.List">
insert into big_temp_data (`time`, region,
`type`, current_period_data, cumulative_data,
indicator_type, time_attribute, `indicator`,period_last_year,period_last,year_on_year,code
)
values
<foreach collection="list" item="item" index="index" separator=",">
( #{item.time,jdbcType=VARCHAR}, #{item.region,jdbcType=VARCHAR},
#{item.type,jdbcType=VARCHAR}, #{item.currentPeriodData,jdbcType=VARCHAR},
#{item.cumulativeData,jdbcType=VARCHAR},
#{item.indicatorType,jdbcType=VARCHAR}, #{item.timeAttribute,jdbcType=VARCHAR},
#{item.indicator,jdbcType=VARCHAR},
#{item.periodLastYear,jdbcType=VARCHAR},#{item.periodLast,jdbcType=VARCHAR},#{item.yearOnYear,jdbcType=VARCHAR},
#{item.code ,jdbcType=VARCHAR}
)
</foreach>
</insert>
注解插入
@Insert({
"<script>",
"insert into task_info(id,info) values ",
"<foreach collection='recordList' item='item' index='index' separator=','>",
"(#{item.id}, #{item.info})",
"</foreach>",
"</script>"
})
int insertList(@Param("recordList") List<TaskInfo> recordList);
Oracle 批量
SQL查询批量
直接用查询出的数据批量为新表赋值,字段名可以不一样,注意顺序
insert into hit_service_catalog_config_items(service_id, catalog_id, create_by, update_by)
select hsc.service_id, hscc.catalog_id, -1, null
from hit_service_catalog_config hscc
left join
hit_service_config hsc on hsc.create_by = hscc.create_by;