SqlBulkCopy与Insert触发器

h576514515 2013-09-02 04:10:41

我原先用sql语句执行插入语句，数据量一大就很慢比如上万条。后来我用SqlBulkCopy插入到SQLSEVER数据中

using (System.Data.SqlClient.SqlBulkCopy sqlBC = new System.Data.SqlClient.SqlBulkCopy(strcon,SqlBulkCopyOptions.FireTriggers))

                {

                    sqlBC.BatchSize = 100000;

                    sqlBC.BulkCopyTimeout = 120;

                    sqlBC.DestinationTableName = "dbo.DX_Customer";

                    sqlBC.ColumnMappings.Add("姓名", "CustomerName");

                    sqlBC.ColumnMappings.Add("性别", "Sex");

                    sqlBC.ColumnMappings.Add("地址", "Address");

                    sqlBC.ColumnMappings.Add("单位", "Company");

                    sqlBC.ColumnMappings.Add("手机", "Mobile");

                    sqlBC.ColumnMappings.Add("电话", "Telephone");

                    sqlBC.ColumnMappings.Add("邮箱", "Email");

                    sqlBC.WriteToServer(dtExcelImport);

                }

在dbo.DX_Customer这个表中有个客户编号这一列，这列是执行只定义函数自动生成的，但是SqlBulkCopy无法执行自定义函数。后来发现使用SqlBulkcopy时在insert触发器的inserted表中是有多条记录的，且一次调用SqlBulkcopy只会有一次触发器的运行。我在触发器给中用游标循环inserted表可是这样数据量一大也会慢

createtrigger [dbo].[CustomerNumber]

on [dbo].[DX_Customer]

After insert 

as

declare @ID int



--定义游标获取符和条件的客户	

	declare cursor_title cursor for

	select ID from inserted

	

	--打开游标

	open cursor_title

	

	--提取游标第一行

	fetch next from cursor_title into @ID

	

	--循环提取游标内容

	while @@FETCH_STATUS=0

		begin

			declare @Code varchar(10)

			declare @Date varchar(4)

			set @Date= SUBSTRING(CONVERT(varchar(100), GETDATE(), 12),1,4)

			select @Code=MAX(CustomerNumber) from DX_Customer where CustomerNumber like ''+@Date+'%' and CustomerNumber is not null

			if @Code>0

				begin

				set @Code=@Code+1

				end

			else

				begin

				set @Code=@Date+'00001'

				end

			update DX_Customer set CustomerNumber=@Code where DX_Customer.ID=@ID

		--获取游标下一行

		fetch next from cursor_title into @ID

		end



	--关闭游标     

    close cursor_title     

    --释放游标资源     

    deallocate cursor_title