Sql Server数据库使用触发器和sqlbulkcopy大批量…

2018-06-23 23:59:13来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

需要了解的知识

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类

示例代码

 1 /// <summary>
 2 /// SqlBulkCopy
 3 /// </summary>
 4 /// <param name="connectionString">目标连接字符</param>
 5 /// <param name="TableName">目标表</param>
 6 /// <param name="dt">数据源</param>
 7 private void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt)
 8 {
 9     using (SqlConnection conn = new SqlConnection(connectionString))
10     {
11         using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.FireTriggers))
12         {
13             try
14             {
15                 sqlbulkcopy.DestinationTableName = TableName;
16                 for (int i = 0; i < dt.Columns.Count; i++)
17                 {
18                     sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
19                 }
20                 sqlbulkcopy.WriteToServer(dt);
21             }
22             catch (System.Exception ex)
23             {
24                 throw ex;
25             }
26         }
27     }
28 }

           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 是否存在相同 id 如果存在,更新 name 字段;如果不存在新增数据到 Table_1。

表 Table_2 主要用于测试触发器内的语句;

Table_3 的触发器

CREATE TRIGGER [dbo].[tri_Insert]  ON [dbo].[Table_3]
INSTEAD OF INSERT 
AS
--存在更新
UPDATE dbo.Table_1 SET id = INSERTED.id,name = INSERTED.NAME
FROM Table_1  JOIN inserted  ON Table_1.id = INSERTED.id

--不存在新增
INSERT dbo.Table_1
        ( id, name )
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 的目的是这样就不用清临时表操作了。

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:使用XmlWriter写Xml

下一篇:怎样在 Azure 应用服务中生成和部署 Java API 应用