What is the correct way to change settings between two tables in SQL

Question

I am looking for a way to non-destructively modify data. I have one table in which there is data that cannot be edited - this is the main data. I would like the user to edit this data, but in order to maintain the integrity of the main data, I created a second table, which is a mirror of the structure of the main table.

My thinking is that I would basically make the view (vAdjustedData) so that this data is merged together, giving me a source that was the most recent version of the data. (The purpose of this is to allow users to cancel their data in the adjusted table and return the data of the main tables as current).

The columns in the tables will be NULL. The idea is that when the user wants to do the editing, at that time the main copy will be copied, the changes will be applied and then saved in the settings table. My idea for UNION would be to mask the original master record with the corrected record, so that when I am placed in the view, I will get the "current" data. When this setting is deleted, the view will be "automatically" rolled back.

So I have Table_Master and Table_Adjusted. Table_Master has a primary key. Table_Adjusted also has a primary key, but it is a foreign key for the primary key Table_Master. If there was a varchar column in both tables, I would expect to write a view similar to this:

(SELECT ID, Value FROM Table_Adjusted) UNION (SELECT ID, Value FROM Table_Master WHERE ID NOT IN (SELECT ID FROM Table_Adjusted)) 

The above UNION should bring all the corrected values, and then all the values ​​from the master who did not have a setup record.

Is this the right way to do this? It seems ineffective to me. I thought EXCEPT would work (using SQL2K8), but that didn't seem appropriate.

Answer 1

I assume that you want the adjusted table to only adjust the values, and not add or remove values. Your query has the disadvantage that it is theoretically possible to add new values ​​that do not even exist in the original table. This version prevents this possible risk:

 SELECT Table_Master.ID, CASE WHEN Table_Adjusted.ID IS NULL THEN Table_Master.Value ELSE Table_Adjusted.Value END AS Value FROM Table_Master LEFT JOIN Table_Adjusted ON Table_Master.ID = Table_Adjusted.ID 

Also your query uses UNION DISTINCT, but in fact you only need UNION ALL. This slightly modified version of your request should run a little faster:

 SELECT ID, Value FROM Table_Adjusted UNION ALL SELECT ID, Value FROM Table_Master WHERE ID NOT IN (SELECT ID FROM Table_Adjusted)