Bulk Copy or Update data from one database to another

Question

I would like to bulk copy or update data from one Database to another by making use of the following criteria:

• Copy the data row if it doesn't already exist based on the barcode column
• Update the current data row in the destination database if the barcode column data already exists

This is my code where I get the data from the local database and BulkCopy it to the central database. How do I add those conditions in my code?

string localConnectionString = GetLocalConnection();
string sqlConnectionStringNP01 = GetNP01Connection();

using (DataTable dt = new DataTable())
{
    using (SqlConnection conn = new SqlConnection(localConnectionString))
    using (SqlCommand cmd = new SqlCommand(sql, conn))
    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
    {
        // Getting Local DB Data
        cmd.CommandType = commandType;
        cmd.Connection.Open();

        adapter.SelectCommand.CommandTimeout = 240;
        adapter.Fill(dt);
        adapter.Dispose();
    }

    // Copying to Central Database
    using (SqlConnection conn2 = new SqlConnection(sqlConnectionStringNP01))
    {
        conn2.Open();

        using (SqlBulkCopy copy = new SqlBulkCopy(conn2))
        {
            // I want to add the code to do the checking here if possible
            copy.DestinationTableName = destinationTable;
            copy.BatchSize = 1000;
            copy.BulkCopyTimeout = 240;
            copy.WriteToServer(dt);
            copy.NotifyAfter = 1000;

            MessageBox.Show("Data successfully transfered to Central Database", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
}

Answer 1

As @Jeroen wrote in his comment, SqlBulkCopy can only insert.

However, you could insert in a temporary table and create a SQL to perform your MERGE statement in your destination table.

Disclaimer: I'm the owner of the project Bulk Operations

This library is not free but offers the BulkMerge option you are looking for.

using (BulkOperation copy = new BulkOperation(conn2))
{
    copy.DestinationTableName = destinationTable;
    copy.BatchSize = 1000;
    copy.BatchTimeout = 240;
    copy.BulkMerge(dt);

    MessageBox.Show("Data successfully transfered to Central Database", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Information);
}