We have a case of bad data coming in from a source (that we cannot influence), and want to create an 'override' table. We would continue to use the majority of the data from the original source, but would have the ability to put rows in the override table as needed to 'fix' things.
How do I intermingle the original source and the override data?
I'm thinking about doing it as shown below, but wanted to get feedback on whether this is the best way, or if there is a best practice I should consider before I start.
pseudo code of current query:
select E.entityId, E.entityName, A.color, A.size from tbl_Entity E inner join tbl_Attribute A on E.entityId = A.entityId
pseudo code incorporating the exception table:
select E.entityId, E.entityName, ISNULL(X.color, A.color) as color, ISNULL(X.size, A.size) as size from tbl_Entity E inner join tbl_Attribute A on E.entityId = A.entityId left join tbl_OverrideAttribute X on E.entityId = X.entityId