Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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...

share|improve this question
1  
Sorry, there is no magic "select the coalesce of two unknown future columns" syntax. When you update the table, you need to update the query. Are you really adding columns to these two tables often enough that this is an issue? Should they really be separate tables? –  Aaron Bertrand Nov 26 '13 at 15:57
    
You can use the columns name and create a generic method, get all from the first and than check if exists on the second... it wont be magic it would be what i am searching, i just dont know how to do so... –  Matan L Nov 26 '13 at 16:02
1  
There is no such thing as a "generic method" in Transact-SQL. You could perhaps use dynamic SQL building the query off of sys.columns but ugh, what a mess to maintain. How many actual query modifications will you be saving? –  Aaron Bertrand Nov 26 '13 at 16:04
    
why is that a mess to maintain ? –  Matan L Nov 26 '13 at 16:10
    
look at my answer. It's even worse in Management Studio because it's all in red, so you can't pick out keywords, IntelliSense can't highlight errors, etc. –  Aaron Bertrand Nov 26 '13 at 16:11
add comment

1 Answer

up vote 3 down vote accepted
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.

share|improve this answer
    
Great this is almost exactly what i needed, i understand you don't recommend this way but i need this, the only problem is that i need that all the other columns that are not in the settings table and not in the extended settings table would also get dynamically .... –  Matan L Nov 26 '13 at 16:25
2  
What columns are not in the settings table and not in the extended settings table but could possibly be returned by this query? Is there another table you haven't mentioned? –  Aaron Bertrand Nov 26 '13 at 16:28
    
No, columns that are in the settings table but not in the extended... –  Matan L Nov 26 '13 at 16:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.