Update / Insert to SQL table using datatable with column mappings

c# datatable insert sql sqlbulkcopy

Question

I'm wanting to do a bulk copy of data from one database to another. It needs to be dynamic enough so that when the users of the source database create new fields, there are minimal changes at the destination end(my end!).

I've done this using the sqlbulkcopy function, using column mappings set up in a seperate table, so that if anything new is created all I need to do is create the new field and set up the mapping (no code or stored procedure changes):

foreach (var mapping in columnMapping)
{
    var split = mapping.Split(new[] { ',' });
    sbc.ColumnMappings.Add(split.First(), split.Last());
}

try
{
    sbc.WriteToServer(sourcedatatable);
}

However, the requirements have now changed.
I need to keep more data, sourced from elsewhere, in other columns in this table which means I can't truncate the whole table and write everything with the sqlbulkcopy. Now, I need to be able to Insert new records or Update the relevant fields for current records, but still be dynamic enough that I won't need code changes if the users create new fields.

Does anyone have any ideas?

Accepted Answer

Comment on original question from mdisibio - it looks like the SQL MERGE statement would have been the answer.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow