I am using SQL server 2012, running on windows data-center 2012, under windows azure cloud platform virtual machine.
I have the following situation :
I have a base settings table lets say as following: ID as a key (int), and many settings column
I have another "extended settings" table that has the following: ID as a key, some information (some settings column), SETTINGS_ID as int related to the settings table ID key
I need a query that would get me the settings from the settings table and would overwrite any value it can find from the extended table that is not null, in a generic way:
Get a settings_id as int, get an "extended_settings" id as int,
Returns a select of all the settings from the settings table by its id but for every setting that is similar by its property name in the extended settings table, and different than null (in the extended table), take the one from there,
I need it to be generic...
I could write the following:
SELECT ISNULL(EXTENDED_SETTINGS.A, SETTINGS.A), ISNULL(EXTENDED_SETTINGS.B, SETTINGS.B), ISNULL(EXTENDED_SETTINGS.C, SETTINGS.C), ISNULL(EXTENDED_SETTINGS.D, SETTINGS.D), SETTINGS.name , SETTINGS.SOME_OTHER_INFO_THATS_NOT_IN_THE_EXTENDED_TABLE FROM SETTINGS INNER JOIN EXTENDED_SETTINGS ON SETTINGS.ID = EXTENDED_SETTINGS.SETTINGS_ID WHERE EXTENDED_SETTINGS.ID = @extSetId AND SETTINGS.ID = @settID
this would work, but i want to make a generic query so that if i add a column named "J" to both tables it would also work, and if i add a column to the settings table it would also work, and i wont have to modify this query every time i change my tables...