Sql-server – Select values from 2 tables with same properties

sql server

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…

Best Answer

DECLARE @sql NVARCHAR(MAX) = N'SELECT ';

SELECT @sql += N'
  ' + QUOTENAME(c.name) 
  + ' = COALESCE(es.' + QUOTENAME(c.name) 
       + ', s.' + QUOTENAME(c.name) + '),'
FROM sys.columns AS c
INNER JOIN sys.columns AS c2
ON c.name = c2.name
AND c.[object_id] = OBJECT_ID('dbo.SETTINGS')
AND c2.[object_id] = OBJECT_ID('dbo.EXTENDED_SETTINGS')
AND c.name NOT IN (N'name', N'ID' /* , ... potentially others ... */);

SET @sql += N' s.name, s.ID';

SELECT @sql += N', 
  s.' + QUOTENAME(c.name)
  FROM sys.columns AS c
  WHERE [object_id] = OBJECT_ID('dbo.SETTINGS')
  AND name NOT IN (N'name', N'ID') 
  AND NOT EXISTS 
  (
    SELECT 1 FROM sys.columns AS c2
      WHERE c2.[object_id] = OBJECT_ID('dbo.EXTENDED_SETTINGS')
      AND c2.name = c.name
  );

SET @sql += N'
  FROM dbo.SETTINGS AS s
  INNER JOIN dbo.EXTENDED_SETTINGS AS es
  ON s.ID = es.SETTINGS_ID
  WHERE es.ID = @extSetId AND s.ID = @settID;';

PRINT @sql;
--EXEC sp_executesql @sql;

Please use table aliases and schema prefixes, and here is why I prefer COALESCE over ISNULL. And if those tables really aren't stored as upper case in a case sensitive collation, do you really need all caps? Also, ID is a horrible and ambiguous name for a column. It should be the same name across the schema; always describing exactly what it is - especially when you're doing lazy dynamic things like this.

Related Question