Zack Zack - 4 months ago 8
SQL Question

Compare row values of a table with column values of another table and if true say true or false

I have two tables I need to compare row values from table 1 with column names of table 2 and if they match then need to update table 2 row values as

true
for the column names matched with row values of table 1. if they dont match then it should be updated with
false
.

Any pointers are highly appreciated.

Answer

You can use INFORMATION_SCHEMA view to obtain column names:

CREATE TABLE ##temp (
    column_name nvarchar(max),
    [flag] int
)

INSERT INTO ##temp
SELECT COLUMN_NAME, 0
FROM YourDB.INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = 'YOURTableName'

UPDATE t
SET [flag] = CASE WHEN a.ColumnToCheck IS NULL THEN 0 ELSE 1 END
FROM ##temp t
LEFT JOIN anotherTable a
    ON t.column_name = a.ColumnToCheck

Then join to your table and update, then pivot:

SELECT *
FROM ##temp
PIVOT (
    MAX([flag]) for column_name in (...)
) as pvt

Better use dynamic SQL to add all column names automatically instead of manualy assigning.

Comments