关于java批量插入sqlServer字段太多问题

最新推荐文章于 2023-05-19 10:20:05 发布

zengwenhe1987

最新推荐文章于 2023-05-19 10:20:05 发布

阅读量692

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/qq_36357608/article/details/103238863

版权

sql 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

报错字段个数不能超过2100

于是批量插入还必须分批插入，单一批次不能超过2100

公式2100/表字段数 = 单个批量插入数量

查询表字段数：

select count(*) from syscolumns s  where s.id = object_id(‘表名’);

然后程序思想：

int colCount = commomMapper.selectColByTableName("emr_fun_group_distribution");
int simpleInsertCount = 2100/colCount;
List<Emr_Fun_Group_Distribution> list = new ArrayList<>();
String[] userIdList = userIds.split(",");
String[] userNameList = userNames.split(",");
for (int i = 0; i < userIdList.length; i++) {
    //处理批量添加sql太长问题，设定个数为180
    if(null != list && i > 0 && list.size()%simpleInsertCount == 0){
        list.clear();
    }
    if(StringUtils.isNotBlank(userIdList[i])){
        Emr_Fun_Group_Distribution distribution = new Emr_Fun_Group_Distribution();
        distribution.setTypeFlag(distributionVo.getTypeFlag());
        distribution.setUserId(Integer.valueOf(userIdList[i]));
        distribution.setUserName(userNameList[i]);
        distribution.setEffective(distributionVo.getEffective());
        distribution.setEffectiveTime(distributionVo.getEffectiveTime());
        distribution.setRemark(distributionVo.getRemark());
        distribution.setCreater(userName);
        distribution.setUpdater(userName);
        distribution.setCreateDate(fmt.format(new Date()));
        distribution.setUpdateDate(fmt.format(new Date()));
        list.add(distribution);
    }
    //是180的倍数或最后一个
    if(null != list && (list.size()%simpleInsertCount == 0 || i == userIdList.length - 1)){
        distributionMapper.SimpleInsert(list);
    }

}