SqlBulkCopy大批量迁移/导入数据

　　近期由于客户需求，需要对数据进行归档，由于单纯的INSERT语句对于百万级别的数据量来说效率是相当的低的，与此同时想到了SqlBulkCopy神器！

     SqlBulkCopy来源于SQL SERVER 一种基于BCP的命令提示符使用工具的托管代码

　　版本：.NET 2.0(想必大家都可以哦!)

　　实现：将数据从源数据复制到目的数据库中的表中

      条件：1.源数据库没有限制，目的数据库必须是SQL SERVER；

　　　　　2.目的表的结构可以和数据源的结构不一致；

　　　　　3.可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。

     SqlBulkCopy主要方法WriteToServer()，将源数据复制到目的数据库表中。

　　用法：

　　　　表：tblSN_Log，tblSN_Log_History,tblSN_History

　　　　　　表tblSN_Log和表tblSN_Log_History的表结构是一致的。

　　　　　

　示例代码：

　　用法主要包括两种方式：

    ///由于SqlDataAdapter需要把数据填充到DataTable中，比较占内存，所有我个人比较喜欢使用SqlDataReader。
    class SqlBulkCopyTest
    {
        //获取数据库连接字符串
        private string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;//源数据库
        private string desConStr = System.Configuration.ConfigurationManager.ConnectionStrings["desConStr"].ConnectionString;//目的数据库
        DateTime startTime;
        /// <summary>
        /// 1.0源数据源和目的数据源的结构相同
        /// </summary>
        public void TableToTable()
        {
            //源数据库
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    comm.Connection = conn;
                    comm.CommandText = "SELECT * FROM tblSN_Log";
                    //comm.CommandType = CommandType.Text;//默认值为CommandType.Text 
                    try
                    {
                        conn.Open();//打开数据库
                        SqlDataReader reader = comm.ExecuteReader();
                        //目的数据库
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(desConStr))
                        {
                            //批次提交的行数,这个值的设置需要根据具体的项目进行测试，没有具体的限定，需要更过测试找到一个平衡点
                            bulkCopy.BatchSize = 100;
                            //设置当处理超过多少行数的时候通知事件SqlRowsCopied
                            bulkCopy.NotifyAfter = 100000;
                            //目的表名
                            bulkCopy.DestinationTableName = "tblSN_Log_History";
                            bulkCopy.SqlRowsCopied += bulkCopy_SqlRowsCopied;
                            startTime = DateTime.Now;//记录开始时间
                            //将源复制到目的表中
                            bulkCopy.WriteToServer(reader);
                        }
                    }
                    catch (Exception ex)
                    {

                        throw ex;
                    }
                    finally
                    {
                        conn.Close();//关闭数据库
                    }
                }
            }
        }
        /// <summary>
        /// 2.0源数据源和目的数据源的结构不一致
        /// </summary>
        public void TableToOtherTable()
        {
            using (SqlConnection conn=new SqlConnection(conStr))
            {
                using (SqlCommand comm=new SqlCommand())
                {
                    comm.Connection = conn;
                    comm.CommandText = "SELECT TOP * FROM tblSN_Log";
                    //comm.CommandType=CommandType.Text //默认值为CommandType.Text 
                    try
                    {
                        conn.Open();//打开数据库
                        SqlDataReader reader = comm.ExecuteReader();
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(desConStr))
                        {
                            bulkCopy.BatchSize = 1000;
                            bulkCopy.DestinationTableName = "tblSN_History";
                            //当目的表和源表的结构不一致的时候，需要进行映射
                            bulkCopy.ColumnMappings.Add("SN_NO", "SN_NO");
                            bulkCopy.ColumnMappings.Add("SN_WAREHOUSE", "DESCRIPTION");//映射列之间的关系(数据类型需要一致)
                            //bulkCopy.SqlRowsCopied += bulkCopy_SqlRowsCopied;
                            //bulkCopy.NotifyAfter = 100000;
                            startTime = DateTime.Now;//记录开始时间
                            //将源复制到目的表中
                            bulkCopy.WriteToServer(reader);
                        }
                    }
                    catch (Exception ex)
                    {

                        throw ex;
                    }
                    finally
                    {
                        conn.Close();//关闭数据库
                    }
                }
            }
        }

        void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {

            string diffTime = (DateTime.Now - startTime).ToString();
            Console.WriteLine(diffTime);
            Console.ReadKey();
        }
    }

两种用法的重要区别在于是否需要进行映射列ColumMapings的操作。

 

经过测试SqlBulkCopy的效率是INSERT语句的近百倍。

心得体会：对于数量级比较高的迁移建议使用SqlBulkCopy，对于数量级别小的可以使用INSERT，在使用INSERT插入多条数据的时候同时注意，最好启用事务，因为默认情况下，SQL Server每次执行INSERT操作的时候都会开启一个事务，在INSERT的时候会先修改呢村中的数据也标识，然后写日志文件，后台有单独的线程负责周期性的检查和修改数据(MDF文件)，这样频繁开启事务性能会相对比较低。如果在一个事务中，将多次对日志文件的修改变成一次修改，性能可以得到了提升。但虽然事务可以改善硬盘的吞吐量，但它也会阻塞其他线程，所以需要进行测试已找到合适的平衡点。