C#批量插入SQLBulkCopy - 如果存在则更新

Question

可能重复：
有没有办法在SQLBulkCopy中执行“如果存在则插入或更新”？

我正在使用SQLBulkCopy来插入大量记录。

如何在已经存在的记录上执行更新（而不是插入）？这是否可以在SQLBulkCopy中实现？

这是我用于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);
}

应用程序细节

1. ASP.net MVC 3.0 Razor视图引擎
2. SQL Server 2008

- HaBo

---

SqlBulkCopy不支持这个操作。使用SqlBulkCopy将数据复制到一个临时表中，然后使用MERGE命令进行合并操作。可通过搜索获得解决方案。 - user166390

https://dev59.com/B0rSa4cB1Zd3GeqPUTax?rq=1，https://dev59.com/rmjWa4cB1Zd3GeqPnh4K#12379039（我的回答，没有详细信息），https://dev59.com/J2445IYBdhLWcg3wZJUn - user166390

在这里搜索“sqlbulkcopy update”。有许多你不能这样做的解答，但其中一个可能会有帮助。 - Tony Hopkinson

Answer 1

感谢 @pst 的建议

根据他的建议，这是我实现的方式，如果有人需要类似的实现。

将数据批量插入永久临时表中

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

然后调用一个存储过程将临时表与实际表合并。

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

这是我在存储过程中使用 Merge 的方法

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

- HaBo