Insert new records and update existing records using SqlBulkCopy in c#

Question

I want to insert new data and update existing one using sqlbulkcopy method.

I have created methods that will always insert records and hence duplicate data are available in table

string ConnectionString = Utility.GetLocalDBConnStr(dbConnModal);
DataTable dt = Utility.ToDataTable(UsersList);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock |
    SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null);

    bulkCopy.DestinationTableName = "User";
    connection.Open();
    bulkCopy.WriteToServer(dt);
    connection.Close();
    res = true;
}

How to achieve this thing? Please suggest any way.

Possible duplicate of [Any way to SQLBulkCopy "insert or update if exists"?](https://stackoverflow.com/questions/4889123/any-way-to-sqlbulkcopy-insert-or-update-if-exists) — steve16351, May 23 '19 at 08:36
Instead of SqlBulkCopy, you could pass the rows as a TVP that is used as a MERGE statement source. — Dan Guzman, May 23 '19 at 09:36
@Krutikapatel bulk doesn't mean batch. There's no `BULK UPDATE` nor does it make sense. `BULK INSERT` means loading a lot of data using minimal logging, as fast as possible. *Updating* would require searching and finding the row to update in the first place — Panagiotis Kanavos, May 23 '19 at 09:36
@Krutikapatel ETL jobs that need to load and update a lot of data use `BULK INSERT` or `SqlBulkCopy to load the data into a staging table and then perform either a MERGE or an UPSERT / INSERT operation to update the existing data and insert the new rows — Panagiotis Kanavos, May 23 '19 at 09:38
@Krutikapatel a staging table is faster than a TVP especially for a lot of data. A TVP has no statistics so the execution plan generated by SQL Server assumes it contains only one row. Obviously, that won't work well for a lot of data. — Panagiotis Kanavos, May 23 '19 at 09:39
Ohh. I got it know. Thank you all of you for your help and suggestions — Krutika Patel, May 23 '19 at 10:35

score 1 · Accepted Answer · answered Aug 22 '20 at 06:39

Please find below stored procedure that will insertdata if not exist and updatedata if exist on empnre01 column bases which is unique id in the table.

Create Proc usp_InsertUpdateAWSUsers
@tblUsers UsersTableType READONLY
AS
BEGIN
SET NOCOUNT ON;
    UPDATE u1
    SET u1.empste01= u2.empste01
        ,u1.[fstnme01] = u2.[fstnme01]
        ,u1.[surnme01] = u2.[surnme01]
        ,u1.[bthcnte01]= u2.[bthcnte01]
        ,u1.[fnce01]   = u2.[fnce01]
        ,u1.[lane01]   = u2.[lane01]
        ,u1.[rsnewe01] = u2.[rsnewe01]
        ,u1.[sxee01]   = u2.[sxee01]
        ,u1.[bthdate01]= u2.[bthdate01]
        ,u1.[nate01]   = u2.[nate01]
    FROM Users u1
    INNER JOIN @tblUsers u2
    ON u1.empnre01=u2.empnre01

    INSERT INTO Users
        (empnre01,empste01,[fstnme01],[surnme01],[bthcnte01],[fnce01],[lane01],     [rsnewe01],[sxee01],[bthdate01],[nate01])
        SELECT u2.empnre01,u2.empste01,u2.[fstnme01],u2.[surnme01],u2.[bthcnte01],u2.[fnce01],u2.[lane01],u2.[rsnewe01]
        ,u2.[sxee01],u2.[bthdate01],u2.[nate01]
        FROM @tblUsers u2
        WHERE empnre01 NOT IN (SELECT empnre01 FROM Users)
    END

Then you can call this SP from C# code like this:

        DataTable dt = List of data to be update in table.
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("usp_InsertUpdateAWSUsers"))
            {
                                 cmd.CommandType = CommandType.StoredProcedure;
                                 cmd.Connection = connection;
                                 cmd.Parameters.AddWithValue("@tblUsers", dt);
                                 connection.Open();
                                 cmd.ExecuteNonQuery();
                                 connection.Close();
                                 res = true;
             }
        }

Insert new records and update existing records using SqlBulkCopy in c#

1 Answers1