Bulk Merge with DataTable

[meeper34]

Hello,

I am trying to use the BulkMerge functionality with a DataTable and without entity framework. I can't figure out how to set the primary key or the MERGE JOIN columns (these are usually set with ColumnPrimaryKeyExpression and ColumnInputExpression).

I can add a single primary key link this: bulkMerge.ColumnMappings.Add(primaryKey, true);

How can I add a primary key that has multiple columns?

Also, how do I set the MERGE JOIN columns? Is there another way to do it without ColumnInputExpression? My table is dynamic and does not have EF entities.

Is this possible?

Thanks, M

[JonathanMagnan]

Hello @meeper34 ,

How can I add a primary key that has multiple columns?

You simply specify more than one column with the primary key parameter to true

bulkMerge.ColumnMappings.Add("key1", true);
bulkMerge.ColumnMappings.Add("key2", true);
bulkMerge.ColumnMappings.Add("key3", true);

Also, how do I set the MERGE JOIN columns? Is there another way to do it without ColumnInputExpression?

I'm not sure to understand this question. Could you elaborate?

Best Regards,

Jonathan

[meeper34]

I'm trying to use the bulk merge functionality with a dynamically created table (I don't have EF entities) into a different table. Both tables, even though from different databases, have matching columns (aside from the IDENTITY columns). Maybe an example would help. Sorry about the long winded response here.

1. I am getting the error "Missing: TestPatientStagingId". I'm guessing this has to do with IgnoreOnMergeInsertExpression and IgnoreOnMergeUpdateExpression not being set? I think I need to ignore the IDENTITY columns, but I don't know how to use these without knowing the table structure up front.
2. Also, how do I set the criteria for which the MERGE ON joins (FirstName and LastName) since ColumnInputExpression requires a hard coded class? See the below MERGE statement. Is there a way to access the underlying structure (similar to using the ColumnMappings list)?
3. Ideally, I would like to be able to merge from different servers non-linked SQL servers, but I don't know if this is possible

use DatabaseA

CREATE TABLE TestPatientStaging( TestPatientStagingId int IDENTITY(1,1) NOT NULL, PatientIdentifier varchar(40) NULL, FirstName varchar(50) NULL, LastName varchar(50) NULL, Address varchar(50) NULL, City varchar(50) NULL, State varchar(3) NULL, ZIP varchar(10) NULL )

insert into TestPatientStaging (PatientIdentifier, FirstName, LastName, Address, City, State, ZIP) values (newid(), 'FirstName', 'LastName', '333 No Street', 'Beverly Hills', 'CA', '90210')

use DatabaseB

CREATE TABLE TestPatient( TestPatientId int IDENTITY(1,1) NOT NULL, PatientIdentifier varchar(40) NULL, FirstName varchar(50) NULL, LastName varchar(50) NULL, Address varchar(50) NULL, City varchar(50) NULL, State varchar(3) NULL, ZIP varchar(10) NULL )

-- The MERGE statement would look like this MERGE INTO TestPatient AS target USING (SELECT PatientIdentifier, FirstName, LastName, Address, City, State, ZIP FROM DatabaseA..TestPatientStaging ) AS source ON SOURCE.FirstName = TARGET.FirstName AND SOURCE.LastName = TARGET.LastName WHEN MATCHED THEN UPDATE SET PatientIdentifier = source.PatientIdentifier, Address = source.Address, City = source.City, State = source.State, ZIP = source.ZIP WHEN NOT MATCHED THEN INSERT (PatientIdentifier, FirstName, LastName, Address, City, State, ZIP) VALUES (source.PatientIdentifier, source.FirstName, source.LastName, source.Address, source.City, source.State, source.ZIP);

var data = new DataTable();

using (var dbConn = new SqlConnection("Data Source=Server;Initial Catalog=DatabaseA;User ID=user;Password=pass;"))
{
    dbConn.Open();

    string sql = "SELECT TOP 1 * FROM TestPatientStaging";

    using (var command = new SqlCommand(sql, dbConn))
    {
        using (var adapter = new SqlDataAdapter(command))
        {
            adapter.Fill(data);

            if (data.Rows.Count == 0)
            {
                return;
            }
        }
    }
}

using (var dbConn = new SqlConnection("Data Source=Server;Initial Catalog=DatabaseB;User ID=user;Password=pass;"))
{
    dbConn.Open();

    var bulkMerge = new BulkOperation(dbConn);

    bulkMerge.ColumnMappings.Add("TestPatientStagingId", true);
    bulkMerge.ColumnMappings.Add("PatientIdentifier", "PatientIdentifier");
    bulkMerge.ColumnMappings.Add("FirstName", "FirstName");
    bulkMerge.ColumnMappings.Add("LastName", "LastName");
    bulkMerge.ColumnMappings.Add("Address", "Address");
    bulkMerge.ColumnMappings.Add("City", "City");
    bulkMerge.ColumnMappings.Add("State", "State");
    bulkMerge.ColumnMappings.Add("ZIP", "ZIP");

    bulkMerge.DestinationTableName = "TestPatient";

    bulkMerge.BulkMerge(data);
}

[JonathanMagnan]

Hello @meeper34 ,

Thank you for the additional information.

I will assign a developer to look at your scenario.

Best Regards,

Jonathan

[JonathanMagnan]

Hello @meeper34 ,

Sorry for the delay in the answer.

I am getting the error "Missing: TestPatientStagingId". I'm guessing this has to do with IgnoreOnMergeInsertExpression and IgnoreOnMergeUpdateExpression not being set? I think I need to ignore the IDENTITY columns, but I don't know how to use these without knowing the table structure up front.

The issue happens because you try to map the key to the column TestPatientStagingId which doesn't exist in your database.

You use the Destination Table TestPatient which doesn't have a column TestPatientStagingId. The key name is TestPatientId

It should be this line

bulkMerge.ColumnMappings.Add("TestPatientStagingId", "TestPatientId", true);

Also, how do I set the criteria for which the MERGE ON joins (FirstName and LastName) since ColumnInputExpression requires a hard coded class? See the below MERGE statement. Is there a way to access the underlying structure (similar to using the ColumnMappings list)?

Sure you can easily do it using this code:

bulkMerge.ColumnMappings.Add("PatientIdentifier", "PatientIdentifier");
bulkMerge.ColumnMappings.Add("FirstName", "FirstName", true);
bulkMerge.ColumnMappings.Add("LastName", "LastName", true);
bulkMerge.ColumnMappings.Add("Address", "Address");
bulkMerge.ColumnMappings.Add("City", "City");
bulkMerge.ColumnMappings.Add("State", "State");
bulkMerge.ColumnMappings.Add("ZIP", "ZIP");

Ideally, I would like to be able to merge from different servers non-linked SQL servers, but I don't know if this is possible

Yes, you can do it.

The DataTable will be loaded from the server A using the Connection A, and you use the Connection B to BulkMerge in the Server B.

Let me know if that answered correctly to your question.

Best Regards,

Jonathan

[meeper34]

Thanks a lot for your help. It works great!