Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>We don't have this exact problem, but we have a very similar problem in which we provide our customers with a baseline set of configuration data (fairly complex, mostly identity PKs) that needs to be updated when we provide a new release.</p> <p>Our mechanism is probably overkill for your situation, but I am sure there is a subset of it that is applicable.</p> <p>The basic approach is this:</p> <p>First, we execute a script that drops all of the FK constraints and changes the nullability of those FK columns that are currently NOT NULL to NULL. This script also drops all triggers to ensure that any logical constraints implemented in them will not be executed.</p> <p>Next, we perform the data import, setting identity_insert off before updating a table, then setting it back on after the data in the table is updated.</p> <p>Next, we execute a script that checks the data integrity of the newly added items with respect to the foreign keys. In our case, we know that items that do not have a corresponding parent record can safely be deleted, but you may choose to take a different approach (report the error and let someone manually handle the issue).</p> <p>Finally, once we have verified the data, we execute another script that restores the nullability, adds the FKs back, and reinstalls the triggers.</p> <p>If you have the budget for it, I would <em>strongly</em> recommend that you take a look at the tools that <a href="http://www.red-gate.com/products/sql-development/" rel="nofollow noreferrer">Red Gate</a> provides, specifically SQL Packager and SQL Data Compare (I suspect there may be other tools out there as well, we just don't have any experience with them). These tools have been critical in the successful implementation of our strategy.</p> <p><strong>Update</strong></p> <p>We provide the baseline configuration through an SQL Script that is generated by RedGate's SQL Packager. </p> <p>Because our end-users can modify the database between updates which will cause the identity values in their database to be different in ours, we actually store the baseline primary and foreign keys in separate fields within each record. </p> <p>When we update the customer database and we need to link new records to known configuration information, we can use the baseline fields to find out what the database-specific FKs should be.</p> <p>In otherwords, there is always a known set of field ids for well-known configuration records regardless what other data is modified in the database and we can use this to link records together.</p> <p>For example, if I have Table1 linked to Table2, Table1 will have a baseline PK and Table2 will have a baseline PK and a baseline FKey containing Table1's baseline PK. When we update records, if we add a new Table2 record, all we have to do is find the Table1 record with the specified baseline PK, then update the actual FKey in Table2 with the actual PK in Table1.</p>
    singulars
    1. This table or related slice is empty.
    1. POCreating snapshot of application data - best practice
      singulars
      1. PTQuestion
    1. PTAnswer
    1. UScompetent_tech
    1. UScompetent_tech
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for the thorough reply. How do you provide the baseline configuration? As an SQL script, XML file, or database files maybe? We have RedGate software in house but that one is used for migrating DB to newer versions by creating a 'difference script' and editing it manually where automated logic cannot cope with the differences. They are really nice products. But as I mentioned the system admin is not allowed to use DB management tools.
      singulars
      1. PO
      1. USDaniel
    2. CO@Daniel: I have updated the answer with additional info.
      singulars
      1. PO
      1. UScompetent_tech
    3. COThank you for providing the update. We may go with a hybrid solution that mixes your ideas with the ones provided by drdigit.
      singulars
      1. PO
      1. USDaniel
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload