SqlBulkCopy 实现原汁原味复制的注意事项_sqlbulkcopyoptions.keepidentity-CSDN博客

本文链接：https://blog.csdn.net/yenange/article/details/35837247

1. 有标识列的表

1. 1 SqlBulkCopyOptions.KeepIdentity 必须设置！否则会出现复制过去的数据产生标识列发现变化的情况！

1.2 如果原表的标识列即为主键，那按1.1 的设置已足够。如果原表无主键，那在复制之前必须先清空原表(truncate table)，否则会出现多个相同的标识值的列！

2. 为NULL值的列

2.1 SqlBulkCopyOptions.KeepNulls 必须设置！否则会出现源数据的字段为NULL时，复制过去却成了默认值！

其它几个选项的说明与分析：

Default 对所有选项使用默认值。
KeepIdentity 保留源标识值。如果未指定，则由目标分配标识值。
CheckConstraints 请在插入数据的同时检查约束。默认情况下，不检查约束。
TableLock 在批量复制操作期间获取批量更新锁。如果未指定，则使用行锁。
KeepNulls 保留目标表中的空值，而不管默认值的设置如何。如果未指定，则空值将由默认值替换（如果适用）。
FireTriggers 指定后，会导致服务器为插入到数据库中的行激发插入触发器。默认情况下，　是不激发触发器的……
UseInternalTransaction 如果已指定，则每一批批量复制操作将在事务中发生。在一个事务中执行，要么都成功，要么都不成功

Default 就没有什么好说的了，不要

KeepIdentity 和 KeepNulls 上面已有了，不再分析。

CheckConstraints 不需要，因为是现成的数据，既然已在DB中，必然是通过了约束检查的。

TableLock 不需要，因为复制时两个库都需要处于单连接状态，不可能有干扰。

FireTriggers 一般就不需要了吧，毕竟只是复制数据，而且是现成的数据……

UseInternalTransaction 关系也不大，反正复制失败会记录到自定义的日志，失败了也知道，重来一次就可以了。

下面是便于测试的代码

SQL:

--1. 建数据来源表
IF EXISTS (  
       SELECT 1 FROM   sysobjects  
       WHERE  id = OBJECT_ID(N'Table_1')  
              AND OBJECTPROPERTY(id, N'IsUserTable') = 1  
   )  
BEGIN  
    DROP TABLE Table_1  
END  
GO  
Create Table Table_1(	
	ID INT, 
	[NAME] VARCHAR(50) DEFAULT('xx')
)
GO
INSERT INTO Table_1
SELECT 1,'a' UNION
SELECT 2,'b' UNION
SELECT 3,'c' UNION
SELECT 4,'d' UNION
SELECT 5,'e' UNION
SELECT 6,null

--2. 建目标表
IF EXISTS (  
       SELECT 1 FROM   sysobjects  
       WHERE  id = OBJECT_ID(N'Table_2')  
              AND OBJECTPROPERTY(id, N'IsUserTable') = 1  
   )  
BEGIN  
    DROP TABLE Table_2  
END  
GO  
Create Table Table_2(	
	ID2 INT, 
	[NAME2] VARCHAR(50) DEFAULT('xx')
)
GO


--复制时的对应关系
--Tabe_1 ==> Table_2
--ID     ==> ID2
--[NAME] ==> [NAME2]

C# 控制台程序：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;

namespace SqlBulkCopyDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string connString = System.Configuration.ConfigurationManager.AppSettings["DCString"];
            SqlConnection ConnectionNew = new SqlConnection(connString);
            SqlConnection ConnectionOld = new SqlConnection(connString);
            bool ExportInfo = System.Configuration.ConfigurationManager.AppSettings["ExportInfo"].ToLower()=="true";
            try
            {
                ConnectionNew.Open();
                ConnectionOld.Open();

                //1.在旧表中，用SqlDataReader读取出信息  
                string SQL = "select * from Table_1";

                SqlCommand cmd = new SqlCommand(SQL, ConnectionOld);
                cmd.CommandTimeout = 7200;
                SqlDataReader sdr = cmd.ExecuteReader();

                //2.初始化SqlBulkCopy对象，用新的连接作为参数。  
                SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnectionNew, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, null);
                bulkCopy.BulkCopyTimeout = 7200;

                //3.写对应关系。如旧表的People列的数据，对应新表Human列，那么就写bulkCopy.ColumnMappings.Add("People","Human")  
                //如果两张表的结构一样，那么对应关系就不用写了。  
                //我是用哈希表存储对应关系的，哈希表作为参数到传入方法中,key的值用来存储旧表的字段名，VALUE的值用来存储新表的值  
                Hashtable ht = new Hashtable();
                ht.Add("ID", "ID2");
                ht.Add("NAME", "NAME2");//  

                foreach (string str in ht.Keys)
                {
                    bulkCopy.ColumnMappings.Add(str, ht[str].ToString());
                }

                //4.设置目标表名  
                bulkCopy.DestinationTableName = "Table_2";

                //额外,可不写：设置一次性处理的行数。这个行数处理完后，会激发SqlRowsCopied()方法。默认为1  
                bulkCopy.NotifyAfter = 1;

                if (ExportInfo)
                {
                    //额外,可不写：设置激发的SqlRowsCopied()方法，这里为bulkCopy_SqlRowsCopied  
                    bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
                }

                //OK，开始传数据！ 
                DateTime dt = DateTime.Now;
                Console.WriteLine("开始时间：{0:yyyy-MM-dd HH:mm:ss,ms}", dt);
                bulkCopy.WriteToServer(sdr);
                DateTime dt2 = DateTime.Now;
                Console.WriteLine("结束时间：{0:yyyy-MM-dd HH:mm:ss,ms}", dt2);
                double time = dt2.Subtract(dt).TotalMilliseconds;
                Console.WriteLine("传输完毕！所用时间为：{0}(ms)", time);
                Console.Read();
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                Console.Read();
            }
            finally
            {
                ConnectionNew.Close();
                ConnectionOld.Close();
            }
        }

        //激发的方法写在外头  
        private static void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            //执行的内容。  
            //这里有2个元素值得拿来用  
            //e.RowsCopied，  //返回数值类型，表示当前已经复制的行数  
            //e.Abort，       //用于赋值true or false，用于停止赋值的操作  
            Console.WriteLine("当前已复制的行数：" + e.RowsCopied);
        }
    }
}

配置文件：

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="DCString" value="Data Source=(local);Initial Catalog=db_Study;Persist Security Info=True;User ID=??;Password=??" />
    <add key="ExportInfo" value="false" />
  </appSettings>
</configuration>