SQL Query to compare dynamic column headers with another static tables columns

Question

I currently import a file dynamically using SSIS into a SQL table, part of the process imports the column headers from the source file into the first row of a table (table 1 below).

I then want to compare the headers from table 1 with table 2 which has static columns, and most importantly highlight any column headers from table 1 that do not exist in table 2.

So for example Table 1 looks like this:

Column 1	Column 2
CustomerID	CustomerName

And table 2 has static column headers like the following:

CustomerID	CustomerName
1	Joe
2	Daniel

So basically when I load a file and a new column header is added (in this example lets say CustomerLocation is added) and loaded into table 1, I want a SQL query to compare the 2 tables and highlight that the column CustomerLocation is missing/does not exist in table 2 currently.

I was thinking along the lines of using the sys tables to compare.

Answer 1

You certainly could use sys.columns to return your static columns from Table2 and compare them to the dynamic columns in Table1 and use UNPIVOT on a select of your first row.

I have found that it was far easier to wrap this all in a T-SQL block and insert to two lists into temp tables before comparing due to data type conflicts (probably be solved by using CAST)

BEGIN
  DECLARE @table1 TABLE (colname VARCHAR(MAX))
  DECLARE @table2 TABLE (colname VARCHAR(MAX))

  INSERT INTO @table1 SELECT COLNAME FROM (SELECT a, b, c FROM TABLE1 WHERE...first row condition) a UNPIVOT (COLNAME FOR COLS IN ([a],[b],[c])) a

  INSERT INTO @table2 SELECT CAST (name  AS NVARCHAR(100)) name FROM sys.columns WHERE object_id = OBJECT_ID('TABLE2')


  SELECT a.colname cols1, b.colname cols2 
    FROM @table2 a
    FULL OUTER JOIN @table1 b ON (a.colname = b.colname)
END

You can easily change the final select to return what you want