SQLTeam.com | Weblogs | Forums

Merge Override Table (pull from tbl_1 first, then tbl_2 if it's not in tbl_1)

#1

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

Thanks!

#2

That looks good to me (other than the obsolete "tbl_". prefixes).

Be sure to cluster the OverrideAttribute table on entityId, for best possible performance overall.