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

Question

这个问题已经在这里有了答案:




已关闭8年。

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

我正在使用 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);
}

申请详情

ASP.net MVC 3.0 Razor View 引擎

SQL Server 2008的

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!
               }
            }

这就是我在存储过程中使用合并的方式

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