Best practice for SQL update cross table, dynamic column RRS feed

  • Question

  • Ok, I've looked over 100's of threads and none of them fit what we're trying to do, so I'll try here.  Firstly, this is a legacy system that we have NO control over, so we're stuck with the infrastructure as-is.

    We have an automated build system for a website/sql backend (utilizing jenkins/WiX) which works fine for the most part.  The SQL database is actually a per-client contained system, so effectively each client runs/hosts their own "version" of the web application/database (pain, I know).  Anyway, occasionally we have to update the structure of the database, in most cases this means adding a column to one or more tables.

    This means, we need an udpate script that will udpate the database schema, and also copy over the customer data (for the tables that actually contain customer-specific data, there are a few), which can result in possible column missing/mismatches, making UPDATE scripts difficult.

    Currently, with our updater, we backup the customer database, create a new "base" database with our latest information, then run udpate scripts on the tables expected to have customer data to copy their live data from the backup to the updated "live" version.  This allows us to update the database even if the schema (mostly) changes, and also allows us to keep a backup of the customer data in case of issues.

    However, our UPDATE scripts fail, obviously, when we introduce a new column to a customer data table.  The old table gets backed up, the new table gets created, however we can't run an UPDATE script on the new schema since the old table (the "source" in this case) does not contain that data.  We also can't assume that all customers will have the same previous version of database, so hardcoded in new columns won't work. 

    The way we are (thinking of) approaching this right now, is to insert the primary key in the new table.  Then go through each column one-by-one and update the fields individually with data.  Having each of these updates in a try/catch means that any failures can effectively be "skipped" and won't disrupt the entire script...however, I'm hoping there's a better way.  Perhaps a way to dynamically determine at install time which fields should be copied, or something similar?

    • Moved by Min Zhu Monday, July 15, 2013 7:54 AM not related to .NET
    Thursday, July 11, 2013 6:06 PM