/****** 对象:  Table [dbo].[Test]    脚本日期: 05/10/2013 11:42:07 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]

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

/// <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);
                }
            }

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);                                           }