Union All拼接Sql语句做批量添加

最新推荐文章于 2022-11-10 18:21:47 发布

灰白色可乐

最新推荐文章于 2022-11-10 18:21:47 发布

阅读量8k

点赞数 3

分类专栏：数据库

本文链接：https://blog.csdn.net/fengyao1995/article/details/50408816

版权

数据库专栏收录该内容

23 篇文章 0 订阅

订阅专栏

上篇文章《多种方法实现Excel批量导入数据库》说到，批量导入用了拼接Insert语句的方式进行，这种方式简单来说是把要导入的每条数据都拼接成一条Insert语句，然后同时执行实现的。但是经过领导提醒，又用了另外一种方式，那就是用Union All做合并，合并完一张表之后，整张表导入。下面就简单介绍一下。

Union All是做联表查询的，平常的用法是：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><span style="font-family:KaiTi_GB2312;font-size:18px;">select * from Table1 union all select * from Table2</span></span>

union all在这里的用法是，查询Table1的时候，先查询Table2的所有字段，将所有字段和Table2合并起来，然后显示出来。union同样也能做这个功能，不过二者有一点区别。union合并的时候，是两张表相同的字段合并为一个字段，而union all做到的是整张表所有的字段都合并给Table1。

在我们这个系统中，是将union all放到了insert语句中，简单介绍一下代码：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><span style="font-family:KaiTi_GB2312;font-size:18px;">        #region 批量添加实体
        /// <summary>
        /// 批量添加实体
        /// </summary>
        /// <param name="settingevaluation">要导入数据库的数据组成的泛型集合</param>
        /// <returns></returns>
        public bool Insert(List<YzSettingEvaluationEntity> settingevaluation)
        {
            //打开数据库连接
            SQLHelper sqlHelper = new SQLHelper("YzEvaluationSystemEntities");
            StringBuilder sbSql = new StringBuilder();

            //循环实体list，进行拼接
            if (settingevaluation.Count > 1)
            {
                sbSql.Append("insert into YzSettingEvaluationEntity(ID,EvaluatorID,CriticID,IsEvaluated,Weight,Year,EvaluationInfoID,IsUsed) ");
                for (int i = 0; i < settingevaluation.Count - 1; i++)
                {
                    sbSql.Append("select ");
                    sbSql.Append("'" + settingevaluation[i].ID + "',");
                    sbSql.Append("'" + settingevaluation[i].EvaluatorID + "',");
                    sbSql.Append("'" + settingevaluation[i].CriticID + "',");
                    sbSql.Append(0 + ",");
                    sbSql.Append("'" + settingevaluation[i].Weight + "',");
                    sbSql.Append("'" + settingevaluation[i].Year + "',");
                    sbSql.Append("'" + settingevaluation[i].EvaluationInfoID + "',");
                    sbSql.Append(1);
                    sbSql.Append(" union all ");
                }
                sbSql.Append("select ");
                sbSql.Append("'" + settingevaluation[settingevaluation.Count - 1].ID + "',");
                sbSql.Append("'" + settingevaluation[settingevaluation.Count - 1].EvaluatorID + "',");
                sbSql.Append("'" + settingevaluation[settingevaluation.Count - 1].CriticID + "',");
                sbSql.Append(0 + ",");
                sbSql.Append("'" + settingevaluation[settingevaluation.Count - 1].Weight + "',");
                sbSql.Append("'" + settingevaluation[settingevaluation.Count - 1].Year + "',");
                sbSql.Append("'" + settingevaluation[settingevaluation.Count - 1].EvaluationInfoID + "',");
                sbSql.Append(1);
                sbSql.Append(";");
            }

            YzSettingEvaluationEntity set = new YzSettingEvaluationEntity();
            var lists = new[] { "ID", "EvaluatorID", "CriticID", "IsEvaluated", "Weight", "Year", "EvaluationInfoID", "IsUsed" };

            //调用sqlhelper方法进行添加。
            int flag = sqlHelper.ExecuteNonQuery(sbSql.ToString(), System.Data.CommandType.Text);
            if (flag > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion</span></span>

直接拼接Insert语句，得到的SQL语句是这个样子的：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><span style="font-family:KaiTi_GB2312;font-size:18px;">insert into YzStaffEntity(ID,StaffName,StaffPassword,StaffID,Sex,IdentityCard,Subject,WorkDate,EngageDate,jobQualification,DivisionID,SeriesID,PositionID,IsUsed) values ('813a3a46-df83-49bf-8b78-d39f6ee73fa8','何志强','151035','1','男','132827196412151035','','1985/8/1 0:00:00','','中教高级','3b253d62-54f9-4dd2-9897-7c8da7107693','e1a5eec0-d61f-4d55-86e2-fbffae1a4fdd','dc1166b7-d394-437c-bf12-a86e488ae930','1' )

insert into YzStaffEntity(ID,StaffName,StaffPassword,StaffID,Sex,IdentityCard,Subject,WorkDate,EngageDate,jobQualification,DivisionID,SeriesID,PositionID,IsUsed) values ('8572db21-930f-4c53-8282-d8dbafd0b9c5','刘怀增','054414','2','男','132801196306054414','','1982/4/1 0:00:00','','','3b253d62-54f9-4dd2-9897-7c8da7107693','e1a5eec0-d61f-4d55-86e2-fbffae1a4fdd','cf0ade2f-3ed7-4fbb-b693-70b98e014ea8','1' )

insert into YzStaffEntity(ID,StaffName,StaffPassword,StaffID,Sex,IdentityCard,Subject,WorkDate,EngageDate,jobQualification,DivisionID,SeriesID,PositionID,IsUsed) values ('0e6b61d4-6fb5-4d83-88bd-e20ba0e2afb3','刘文俊','170819','3','男','132801196110170819','','1986/7/1 0:00:00','','中教高级','3b253d62-54f9-4dd2-9897-7c8da7107693','e1a5eec0-d61f-4d55-86e2-fbffae1a4fdd','cf0ade2f-3ed7-4fbb-b693-70b98e014ea8','1' )</span></span>

而用union all拼接完的SQL语句的样子是这样的：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><span style="font-family:KaiTi_GB2312;font-size:18px;">insert into YzSettingEvaluationEntity(ID,EvaluatorID,CriticID,IsEvaluated,Weight,Year,EvaluationInfoID,IsUsed) select 'd000917f-9ebd-45e5-a574-0f2fa57539ad','63eff2c1-0dc8-4822-8664-1e54d9e632ec','439b6794-7467-4623-a8f6-0756c12e3eb5',0,'f87680ce-1cb4-d2d0-7aa5-04a6c5c3edf6','621541d6-1697-fb82-fc14-8540ff1cf000','0b2bc4cd-ac5f-412e-a227-c3b4ac61ddc5',1 union all 

select '6977b00d-94ea-442f-b22e-fd296c54d2e1','63eff2c1-0dc8-4822-8664-1e54d9e632ec','51c11fd2-65ca-4c61-9e13-0c44c735e4bd',0,'f87680ce-1cb4-d2d0-7aa5-04a6c5c3edf6','621541d6-1697-fb82-fc14-8540ff1cf000','0b2bc4cd-ac5f-412e-a227-c3b4ac61ddc5',1 union all 

select '303de911-52cc-4e20-ba88-6b301c59728a','63eff2c1-0dc8-4822-8664-1e54d9e632ec','09bd256e-eda4-47e4-bbe6-0e68eb472659',0,'f87680ce-1cb4-d2d0-7aa5-04a6c5c3edf6','621541d6-1697-fb82-fc14-8540ff1cf000','0b2bc4cd-ac5f-412e-a227-c3b4ac61ddc5',1 union all; 
</span></span>

关于执行速度上面，还是Insert语句快一点点，经过测试，直接拼接Insert语句的，插入6400条数据花费21秒；而union all这种方式插入5550条数据花费了19秒，性能上二者相差不是很大，简单的导入用哪种方式都是可以的。

在我们拼接SQL语句的过程中，还用过一种拼接Insert语句，拼接完的效果是：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><span style="font-family:KaiTi_GB2312;font-size:18px;">insert into YzStaffEntity(ID,StaffName,StaffPassword,StaffID,Sex,IdentityCard,Subject,WorkDate,EngageDate,jobQualification,DivisionID,SeriesID,PositionID,IsUsed) values ('813a3a46-df83-49bf-8b78-d39f6ee73fa8','何志强','151035','1','男','132827196412151035','','1985/8/1 0:00:00','','中教高级','3b253d62-54f9-4dd2-9897-7c8da7107693','e1a5eec0-d61f-4d55-86e2-fbffae1a4fdd','dc1166b7-d394-437c-bf12-a86e488ae930','1' ),

 ('8572db21-930f-4c53-8282-d8dbafd0b9c5','刘怀增','054414','2','男','132801196306054414','','1982/4/1 0:00:00','','','3b253d62-54f9-4dd2-9897-7c8da7107693','e1a5eec0-d61f-4d55-86e2-fbffae1a4fdd','cf0ade2f-3ed7-4fbb-b693-70b98e014ea8','1' ),

 ('0e6b61d4-6fb5-4d83-88bd-e20ba0e2afb3','刘文俊','170819','3','男','132801196110170819','','1986/7/1 0:00:00','','中教高级','3b253d62-54f9-4dd2-9897-7c8da7107693','e1a5eec0-d61f-4d55-86e2-fbffae1a4fdd','cf0ade2f-3ed7-4fbb-b693-70b98e014ea8','1' )
</span></span>

这样的SQL语句有一个弊病，那就是插入的数据超过千条后，就提示插入不成功，因为一条Insert语句插入的数据量是不能超过一千条的，所以如果有这个问题，可以选择上面介绍的任意一种方式来解决。

总结

记忆很深的有一节米老师讲的课，主题是人生如代码，代码如人生，越来越能体会到这句话的含义。你的代码就像你的人生一样，人生路上有很多个岔路口，每经过一个岔路口都需要你做出选择，代码也一样。实现同一个功能的方式有很多，关键是要选择最适合的那个。而且代码需要用心去写，就像你的路需要用心去走一样。

灰白色可乐

关注

3
点赞
踩
4

收藏

觉得还不错? 一键收藏
87
评论
Union All拼接Sql语句做批量添加

上篇文章《多种方法实现Excel批量导入数据库》说到，批量导入用了拼接Insert语句的方式进行，这种方式简单来说是把要导入的每条数据都拼接成一条Insert语句，然后同时执行实现的。但是经过领导提醒，又用了另外一种方式，那就是用Union All做合并，合并完一张表之后，整张表导入。下面就简单介绍一下。 Union All是做联表查询的，平常的用法是：&lt;sp...
复制链接

扫一扫