原文:SqlBulkCopy与触发器，批量插入表(存在则更新,不存在则插入)
临时表：Test
/****** 对象: Table [dbo].[Test] 脚本日期: 05/10/2013 11:42:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[UserName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
临时表的触发器：tri_edit
CREATE TRIGGER [tri_edit] ON [dbo].[Test] instead of insert as declare @temp int select @temp= inserted.UserID from inserted
--更新已经存在的主键(更新全部字段)
if(@temp IS Not NULL )
update [Test] set UserQQ=inserted.UserQQ
from [Test] join inserted on [Test].UserID=inserted.UserID
else --(更新指定的部分字段)
update [Test] set UserName=inserted.UserName
from [Test] join inserted on [Test].UserID=inserted.UserID
--插入存在的主键数据
insert [Test] (UserID,UserName)
select inserted.UserID,inserted.UserName
from inserted left join [Test] on inserted.UserID=[Test].UserID
where [Test].id is null
go
C# SqlBulkCopy方法：
/// <summary>
/// SqlBulkCopy往数据库中批量插入数据
/// </summary>
/// <param name="sourceDataTable">数据源表</param>
/// <param name="targetTableName">服务器上目标表</param>
/// <param name="mapping">创建新的列映射，并使用列序号引用源列和目标列的列名称。</param>
public static void BulkToDB(DataTable sourceDataTable, string targetTableName, SqlBulkCopyColumnMapping[] mapping)
{
/* 调用方法 - 2013年05月10日编写
//DataTable dt = Get_All_RoomState_ByHID();
//SqlBulkCopyColumnMapping[] mapping = new SqlBulkCopyColumnMapping[4];
//mapping[0] = new SqlBulkCopyColumnMapping("Xing_H_ID", "Xing_H_ID");
//mapping[1] = new SqlBulkCopyColumnMapping("H_Name", "H_Name");
//mapping[2] = new SqlBulkCopyColumnMapping("H_sName", "H_sName");
//mapping[3] = new SqlBulkCopyColumnMapping("H_eName", "H_eName");
//BulkToDB(dt, "Bak_Tts_Hotel_Name", mapping);
*/
using (SqlConnection conn = new SqlConnection(DBHelper.ConnectionString))
{
//SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //用其它源的数据有效批量加载sql server表中
//指定大容量插入是否对表激发触发器。此属性的默认值为 False。
SqlBulkCopy bulkCopy = new SqlBulkCopy(DBHelper.ConnectionString, SqlBulkCopyOptions.FireTriggers);
bulkCopy.DestinationTableName = targetTableName; //服务器上目标表的名称
bulkCopy.BatchSize = sourceDataTable.Rows.Count; //每一批次中的行数
//bulkCopy.BulkCopyTimeout = 300; //超时之前操作完成所允许的秒数,大批量数量需要的时长5分钟,2013-11-6备注 报错：“超时时间已到。在操作完成之前超时时间已过或服务器未响应” 解决办法：
if (sourceDataTable != null && sourceDataTable.Rows.Count != 0)
{
for (int i = 0; i < mapping.Length; i++)
bulkCopy.ColumnMappings.Add(mapping[i]);
//将提供的数据源中的所有行复制到目标表中
bulkCopy.WriteToServer(sourceDataTable);
}
}
C# 调用BulkToDB方法：
int kk = Environment.TickCount;
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("UserID", typeof(string)));
dt.Columns.Add(new DataColumn("UserName", typeof(string)));
for (int k = 0; k < 10000; k++) //40000
{
DataRow dr = dt.NewRow();
dr["UserID"] = k;
dr["UserName"] = "8888 - " + k;
dt.Rows.Add(dr);
}
SqlBulkCopyColumnMapping[] mapp = new SqlBulkCopyColumnMapping[2];
mapp[0] = new SqlBulkCopyColumnMapping("UserID", "UserID");
mapp[1] = new SqlBulkCopyColumnMapping("UserName", "UserName");
//提交任务表
BulkToDB(dt, "Test", mapp);
int exeg = Environment.TickCount - kk;
MessageBox.Show(exeg.ToString());
动态加载数据列名：
SqlBulkCopyColumnMapping[] mapp = new SqlBulkCopyColumnMapping[dt.Columns.Count];
for (int j = 0; j < dt.Columns.Count; j++)
{
mapp[j] = new SqlBulkCopyColumnMapping(dt.Columns[j].ColumnName, dt.Columns[j].ColumnName);
}
1、--处理的触发器示例
create trigger tr_insert on 表
instead of insert --注意触发器的类型
as
--更新已经存在的主键
update 表 set name=b.name,sex=b.sex
from 表 a join inserted b on a.id=b.id
--插入存在的主键数据
insert 表
select a.*
from inserted a left join 表 b on a.id=b.id
where b.id is null
go
——————————————————————————————————————————
2、--触发器
CREATE TRIGGER tri_edit ON tab
INSTEAD OF INSERT
AS
if exists(select col1,col2 from tab join inserted on tab.学号=INSERTED.学号)
begin
--这里面你可以加如些其他修改操作,取决于具体的功能
update tab set col1='num1' from tab join inserted on tab.学号=inserted.学号
end
else
insert tab select * from inserted
GO