Select column names based on condition in two separate table and database-CodePudding

I have the same table in two separate database (schema can be different so I use 'intersect') :

MyTable from Database1
MyTable from Database2

I want to get the column name, and if possible data, when the row of the current column has NULL value in Database1 and NOT NULL in Database2.

The query can looks like this :

SELECT (
    SELECT COLUMN_NAME FROM Database1.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='MyTable' AND TABLE_SCHEMA='dbo'
    INTERSECT
    SELECT COLUMN_NAME FROM Database2.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='MyTable' AND TABLE_SCHEMA='dbo'
) /*  data ? */
FROM Database1.dbo.MyTable AS MyTable1 INNER JOIN
Database2.dbo.MyTable AS MyTable2 ON MyTable1.id = MyTable2.id
WHERE MyTable1.COLUMN_NAME IS NULL AND MyTable2.COLUMN_NAME IS NOT NULL
OR MyTable2.COLUMN_NAME IS NULL AND MyTable1.COLUMN_NAME IS NOT NULL

But this is obviously wrong.

CodePudding user response：

You need dynamic SQL for this, and it won't be pretty.

DECLARE @sql     nvarchar(max) = N'SELECT t1.id',
        @sel     nvarchar(max) = N'', 
        @clauses nvarchar(max) = N'';
  
;WITH src(col, name) AS
(
      SELECT QUOTENAME(c.name), c.name 
        FROM Database1.sys.columns AS c
        INNER JOIN Database1.sys.tables AS t
        ON c.[object_id] = t.[object_id]
        WHERE t.name = N'MyTable' AND t.schema_id = 1
      INTERSECT 
      SELECT QUOTENAME(c.name), c.name 
        FROM Database2.sys.columns AS c
        INNER JOIN Database2.sys.tables AS t
        ON c.[object_id] = t.[object_id]
        WHERE t.name = N'MyTable' AND t.schema_id = 1
),
clauses(clause, sel) AS
(
  SELECT clause = char(13)   char(10)
        N'  OR (   (t1.'   col   N' IS NULL AND t2.'   col   N' IS NOT NULL)'
        char(13)   char(10) 
        '       OR (t1.'   col   N' IS NOT NULL AND t2.'   col   N' IS NULL))',
    sel = N','   char(13)   char(10) 
        N'  t1_'   name   N' = t1.'   col 
        N', t2_'   name   N' = t2.'   col
  FROM src
  WHERE name <> N'id'
)
SELECT @sel  = sel, @clauses  = clause FROM clauses;
  
SELECT @sql  = @sel 
     char(13)   char(10)   N' FROM Database1.dbo.MyTable AS t1'
     char(13)   char(10)   N' INNER JOIN Database2.dbo.MyTable AS t2'
     char(13)   char(10)   N' ON t1.id = t2.id AND (1 = 2'   @clauses   N');';
  
SELECT @sql;
--EXEC sys.sp_executesql @sql;

Output (assuming MyTable has common columns id, x, and y):

SELECT t1.id,
  t1_x = t1.[x], t2_x = t2.[x],
  t1_y = t1.[y], t2_y = t2.[y]
 FROM Database1.dbo.MyTable AS t1
 INNER JOIN Database2.dbo.MyTable AS t2
 ON t1.id = t2.id AND (1 = 2
  OR (   (t1.[x] IS NULL AND t2.[x] IS NOT NULL)
       OR (t1.[x] IS NOT NULL AND t2.[x] IS NULL))
  OR (   (t1.[y] IS NULL AND t2.[y] IS NOT NULL)
       OR (t1.[y] IS NOT NULL AND t2.[y] IS NULL)));