C＃批量插入SQLBulkCopy - 如果存在更新 [英] C# Bulk Insert SQLBulkCopy - Update if Exists

问题描述

可能重复：

任何方式到SQLBulkCopy插入或更新如果存在

我使用 SQLBulkCopy 插入批量记录

对已经存在的记录执行更新（而不是插入）？这是可能与 SQLBulkCopy ？

这是我的代码SQLBulkCopy

 使用（var bulkCopy = new SqlBulkCopy（ConfigurationManager.ConnectionStrings [ConnectionString]。ConnectionString，SqlBulkCopyOptions.KeepNulls& SqlBulkCopyOptions.KeepIdentity））
 {
 bulkCopy.BatchSize = CustomerList.Count; 
 bulkCopy.DestinationTableName =dbo.tCustomers; 
 bulkCopy.ColumnMappings.Clear（）; 
 bulkCopy.ColumnMappings.Add（CustomerID，CustomerID）; 
 bulkCopy.ColumnMappings.Add（FirstName，FirstName）; 
 bulkCopy.ColumnMappings.Add（LastName，LastName）; 
 bulkCopy.ColumnMappings.Add（Address1，Address1）; 
 bulkCopy.ColumnMappings.Add（Address2，Address2）; 
 bulkCopy.WriteToServer（CustomerList）; 
} 
  

申请详情

1. ASP.net MVC 3.0 Razor视图引擎


2. SQL Server 2008

解决方案

感谢@pst

他的建议这是我实现的，如果任何人必须实现类似。 p>

批量插入永久临时表

 使用（var bulkCopy = new SqlBulkCopy（ConfigurationManager.ConnectionStrings [ConnectionString]。ConnectionString，SqlBulkCopyOptions.KeepNulls& SqlBulkCopyOptions.KeepIdentity））
 {
 bulkCopy.BatchSize = CustomerList.Count; 
 bulkCopy.DestinationTableName =dbo.tPermanentTempTable; 
 bulkCopy.ColumnMappings.Clear（）; 
 bulkCopy.ColumnMappings.Add（CustomerID，CustomerID）; 
 bulkCopy.ColumnMappings.Add（FirstName，FirstName）; 
 bulkCopy.ColumnMappings.Add（LastName，LastName）; 
 bulkCopy.ColumnMappings.Add（Address1，Address1）; 
 bulkCopy.ColumnMappings.Add（Address2，Address2）; 
 bulkCopy.WriteToServer（CustomerList）; 
} 
  

然后调用存储过程来合并临时表与实际表

  using（Entities context = new Entities（））
 {
 System.Nullable< int> iReturnValue = context.usp_Customer_BulkUploadMerge（customerid，locationID）.SingleOrDefault（）; 
 if（iReturnValue.HasValue）
 {
 //返回成功！ 
} 
} 
  

这是我如何使用Merge在我的存储过程

  ALTER程序usp_Customer_BulkUploadMerge 
（
 @CustomerID INT，
 @locationID INT 
 ）
 AS 
 BEGIN 
 DECLARE @retValue INT 
 BEGIN TRY 
如果OBJECT_ID（'tCustomers'）不是NULL 
 BEGIN 
 BEGIN TRANSACTION MergPatientsTable 
 SET NOCOUNT ON; 
 MERGE dbo.tCustomers AS target 
 USING 
（SELECT PU.CustomerID，
 PU.LocationID，
 PU.FirstName，
 PU.LastName，
 PU.MiddleInitial，
 PU.Gender，
 PU.DOB 
 
 FROM dbo.tPermanentTempTable PU 
 WHERE PU.CustomerID = @CustomerID 
和PU.LocationID = @locationID 
 GROUP BY PU.CustomerID，
 PU.LocationID，
 PU.FirstName，
 PU.LastName，
 PU.MiddleInitial，
 PU.Gender，
 PU.DOB 
 
）AS源（CustomerID，LocationID，FirstName，
 LastName，MiddleInitial，Gender，DOB）
 ON LOWER（target.FirstName）= LOWER（source.FirstName）
 AND LOWER（target.LastName）= LOWER（source.LastName）
 AND target.DOB = source.DOB 
）
 WHEN MATCHED 
 THEN 
 UPDATE SET 
 MiddleInitial = source.MiddleInitial，
 Gender = source.Gender，
 LastActive = GETDATE（）
 WHEN NOT MATCHED 
 THEN 
 INSERT（
 CustomerID，
 LocationID，
 FirstName，
 LastName，
 MiddleInitial，
性别，
 DOB，
 DateEntered，
 LastActive 
）VALUES 
（source.CustomerID，
 source.LocationID，
 source.FirstName，
 source.LastName，
 source.MiddleInitial，
 source.Gender，
 source.DOB，
 GETDATE（），
 NULL 
）; 
 DELETE PU 
 FROM dbo.tPermanentTempTable PU 
 WHERE PU.CustomerID = @CustomerID 
和PU.LocationID = @locationID 
 COMMIT TRANSACTION MergPatientsTable 
 SET @ retValue = 1 
 SELECT @retValue 
 END 
 ELSE 
 BEGIN 
 SET @retValue = -1 
 SELECT @retValue 
 END 
 END TRY 
 BEGIN CATCH 
 ROLLBACK TRANSACTION MergPatientsTable 
 DECLARE @ErrorMsg VARCHAR（MAX）; 
 DECLARE @ErrorSeverity INT; 
 DECLARE @ErrorState INT; 
 SET @ErrorMsg = ERROR_MESSAGE（）; 
 SET @ErrorSeverity = ERROR_SEVERITY（）; 
 SET @ErrorState = ERROR_STATE（）; 
 SET @retValue = 0 
 SELECT @retValue 
  -  SELECT 0 AS isSuccess 
 END CATCH 
 END 
  

Possible Duplicate:
Any way to SQLBulkCopy “insert or update if exists”?

I am using SQLBulkCopy to insert Bulk records

How can I perform on update (rather than an insert) on records that already exist? Is this possible with SQLBulkCopy?

This is my code for SQLBulkCopy

using (var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, SqlBulkCopyOptions.KeepNulls & SqlBulkCopyOptions.KeepIdentity))
{
    bulkCopy.BatchSize = CustomerList.Count;
    bulkCopy.DestinationTableName = "dbo.tCustomers";
    bulkCopy.ColumnMappings.Clear();
    bulkCopy.ColumnMappings.Add("CustomerID", "CustomerID");
    bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
    bulkCopy.ColumnMappings.Add("LastName", "LastName");
    bulkCopy.ColumnMappings.Add("Address1", "Address1");
    bulkCopy.ColumnMappings.Add("Address2", "Address2");
    bulkCopy.WriteToServer(CustomerList);
}

Application Details

1. ASP.net MVC 3.0 Razor view Engine
2. SQL Server 2008

解决方案

Thanks to @pst

with his suggestions this is how I implemented, if anyone has to implement similar.

Bulk Insert in to permanent Temp Table

 using (var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, SqlBulkCopyOptions.KeepNulls & SqlBulkCopyOptions.KeepIdentity))
            {
                bulkCopy.BatchSize = CustomerList.Count;
                bulkCopy.DestinationTableName = "dbo.tPermanentTempTable";
                bulkCopy.ColumnMappings.Clear();
                bulkCopy.ColumnMappings.Add("CustomerID", "CustomerID");
                bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                bulkCopy.ColumnMappings.Add("LastName", "LastName");
                bulkCopy.ColumnMappings.Add("Address1", "Address1");
                bulkCopy.ColumnMappings.Add("Address2", "Address2");
                bulkCopy.WriteToServer(CustomerList);
            } 

Then call a stored Procedure to Merge the temp table with actual table

 using (Entities context = new Entities())
            {
               System.Nullable<int> iReturnValue = context.usp_Customer_BulkUploadMerge(customerid, locationID).SingleOrDefault();
               if (iReturnValue.HasValue)
               {
                   // return was successful!
               }
            }

This is how I used Merge in my Stored Procedure

ALTER PROCEDURE usp_Customer_BulkUploadMerge
    (
      @CustomerID INT ,
      @locationID INT
    )
AS 
    BEGIN
    DECLARE @retValue INT
        BEGIN TRY
            IF OBJECT_ID('tCustomers') IS NOT NULL 
                BEGIN
                    BEGIN TRANSACTION MergPatientsTable
                    SET NOCOUNT ON;
                    MERGE dbo.tCustomers AS target
                        USING 
                            ( SELECT    PU.CustomerID ,
                                        PU.LocationID ,
                                        PU.FirstName ,
                                        PU.LastName ,
                                        PU.MiddleInitial ,
                                        PU.Gender ,
                                        PU.DOB

                              FROM      dbo.tPermanentTempTable PU
                              WHERE     PU.CustomerID = @CustomerID
                                        AND PU.LocationID = @locationID
                              GROUP BY  PU.CustomerID ,
                                        PU.LocationID ,
                                        PU.FirstName ,
                                        PU.LastName ,
                                        PU.MiddleInitial ,
                                        PU.Gender ,
                                        PU.DOB 

                            ) AS source ( CustomerID, LocationID, FirstName,
                                          LastName, MiddleInitial, Gender, DOB )
                        ON ( LOWER(target.FirstName) = LOWER(source.FirstName)
                             AND LOWER(target.LastName) = LOWER(source.LastName)
                             AND target.DOB = source.DOB
                           )
                        WHEN MATCHED 
                            THEN 
        UPDATE                SET
                MiddleInitial = source.MiddleInitial ,
                Gender = source.Gender,               
                LastActive = GETDATE()
                        WHEN NOT MATCHED 
                            THEN    
        INSERT  (
                  CustomerID ,
                  LocationID ,
                  FirstName ,
                  LastName ,
                  MiddleInitial ,
                  Gender ,
                  DOB ,
                  DateEntered ,
                  LastActive
                )             VALUES
                ( source.CustomerID ,
                  source.LocationID ,
                  source.FirstName ,
                  source.LastName ,
                  source.MiddleInitial ,
                  source.Gender ,
                  source.DOB ,
                  GETDATE() ,
                  NULL
                );
                    DELETE  PU
                    FROM    dbo.tPermanentTempTable PU
                    WHERE   PU.CustomerID = @CustomerID
                            AND PU.LocationID = @locationID 
                    COMMIT TRANSACTION MergPatientsTable
                    SET @retValue = 1
                    SELECT @retValue
                END
            ELSE 
                BEGIN
                SET @retValue = -1
                    SELECT  @retValue
                END
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION MergPatientsTable
            DECLARE @ErrorMsg VARCHAR(MAX);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;
            SET @ErrorMsg = ERROR_MESSAGE();
            SET @ErrorSeverity = ERROR_SEVERITY();
            SET @ErrorState = ERROR_STATE();
        SET @retValue = 0
        SELECT @retValue
           -- SELECT  0 AS isSuccess
        END CATCH
    END

这篇关于C＃批量插入SQLBulkCopy - 如果存在更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！