2

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?

csharpwinphonexaml
  • 3,659
  • 10
  • 32
  • 63
csharper
  • 29
  • 2
  • Distinguishing between the two parts of your question: 1) dynamic column mappings and 2)how to insert and/or update instead of truncate/bulk-copy: insert/update is best solved by the `MERGE` statement. It is pretty flexible, and since you can define a source and target, you might be able to dynamically write the full sql using your column mapping data. Alternatively, you could bulk insert your source data using your current methods into a temp table, and then call a procedure (that you might have to update when new columns are added) that merges it with the production table you need to keep. – mdisibio Apr 27 '14 at 21:10
  • Thanks for your comment. I had a look at the merge statement, and it looked like it could have done the job for me. Unfortunately, requirements and priorities changed again, so it's been put on hold for now. Thanks again! – csharper May 02 '14 at 09:39

1 Answers1

0

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

csharper
  • 29
  • 2