C# Bulk Insert SQLBulkCopy - Update if Exists [duplicate]

Question

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

Answer 1

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