Sql Server数据库使用触发器和sqlbulkcopy大批量数据插入更新

ienki

需要了解的知识
　　1.触发器
　　2.sqlbulkcopy

我的用途
　　开发数据库同步的工具，需要大批量数据插入和数据更新。

方式
　　使用SqlBulkCopy类对数据进行数据批量复制，将需要同步数据的表新建一个结构相同的临时表。然后在临时表编写触发器用于对更新数据的表进行数据插入和更新；

触发器要点
　　1.触发器(trigger)是一种特殊的存储过程，当你对一个表进行Insert、Delete或Update时进行触发；
　　2.触发器触发时自动在系统创建inserted和deleted表，触发器执行完成后自动删除：
　　inserted存放，新增和更新的数据；
　　deleted表存放删除和更新前的数据；
　　3.AFTER和INSTEAD OF是两种触发器：
　　AFTER是在完成执行Insert、Delete或Update才进行触发；INSTEAD OF 触发器的动作要早于表的约束处理；
　　触发器内容参考   http://www.cnblogs.com/selene/p/4493311.html

SqlBulkCopy类
　　示例代码
　　

/// <summary>　　
/// SqlBulkCopy
　　
/// </summary>
　　
/// <param name="connectionString">目标连接字符</param>
　　
/// <param name="TableName">目标表</param>
　　
/// <param name="dt">数据源</param>
　　
private void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt)
　　
{
　　
     using (SqlConnection conn = new SqlConnection(connectionString))
　　
     {
　　
         using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.FireTriggers))
　　
         {
　　
             try
　　
             {
　　
                 sqlbulkcopy.DestinationTableName = TableName;
　　
                 for (int i = 0; i < dt.Columns.Count; i++)
　　
                 {
　　
                     sqlbulkcopy.ColumnMappings.Add(dt.Columns.ColumnName, dt.Columns.ColumnName);
　　
                 }
　　
                 sqlbulkcopy.WriteToServer(dt);
　　
             }
　　
             catch (System.Exception ex)
　　
             {
　　
                 throw ex;
　　
             }
　　
         }
　　
     }
　　
}
　　

　　MSDN介绍     
　　特别说明的是
　　SqlBulkCopyOptions 属性设置为FireTriggers，是来激活对应表触发器的；

实例
　　新建三张表，结构非常简单；
　　

SELECT  Table_1.id ,　　Table_1.name
　　

FROM    dbo.Table_1;　　

　　

SELECT  Table_2.id ,　　Table_2.name
　　

FROM    dbo.Table_2;　　

　　

SELECT  Table_3.id ,　　Table_3.name
　　

FROM    dbo.Table_3;　　

　　创建表
　　

CREATE TABLE [dbo].[Table_1]　　(
[id] [NCHAR](10) NULL ,[name] [NCHAR](10) NULL　　)
　　

ON  [PRIMARY];　　

　　当向 Table_3 插入数据时，

　　判断数据在 Table_1 是否存在相同>　　表 Table_2 主要用于测试触发器内的语句；

Table_3 的触发器
　　

CREATE TRIGGER [dbo].[tri_Insert]  ON [dbo].[Table_3]　　
INSTEAD
OF INSERT　　
AS
　　


　　
UPDATE dbo.Table_1 SET>　　
FROM Table_1  JOIN inserted  ON Table_1.id = INSERTED.id
　　

　　

　　
INSERT dbo.Table_1

　　(>　　
SELECT
　　INSERTED.id,INSERTED.name
　　FROM INSERTED
　　LEFT OUTER  JOIN Table_1  ON INSERTED.id = Table_1.id
　　
WHERE Table_1.ID IS NULL
　　

　　这个触发器实现了当向 Table_3 表插入数据时判断 Table_1 是否存在这条数据，存在则更新，不存在则新增的目的；
　　对于新增更新这两条语句，可以在Table_2 进行测试为何这样写，原理非常简单；

FQA
　　SqlBulkCopy批量插入的DataTable要求：
　　插入的DataTable必须与目标表结构相同，我的做法是先 SELECT * FROM  TableName WHERE 1=2  获取的表结构；
　　SqlBulkCopy主键是否自动更新，相同主键是否更新：
　　我经过测试，主键标识列在Insert中置空后自动自增，相同主键也不会主动更新，而是新增；
　　同时当新增碰到标识约束时，插入相同标识时会返回异常；及时赋相同ID值也会新标识相同无法更新；
　　AFTER和INSTEAD OF两种触发器的不同
　　在向触发器批量更新数据时，INSTEAD OF是将数据直接交给触发器执行（动作要早于表的约束处理？？），触发器语句如果没有对表新增的语句则不会新增数据；（？？理解是否有偏差？？）
　　而AFTER是对表操作完成后才执行触发器；
　　大批量插入更新操作使用 INSTEAD OF 的目的是这样就不用清临时表操作了。