In SQL Server, how can I compare rows using the column names from another table

Question

I have two tables A and B, with dynamic columns where I have no idea what columns are key inside them, except from another table called C.

The C table specifies which column/s is a key column in tables A and B. There can be 1 or more key columns.

My question is, how would I generate such a query where I select all rows from A where the key columns are equal to the same key columns in B?

One idea I had was to create a text query that I execute with sp_executesql, but I need some good ideas on how to generate the query.

First of all, I would select all key columns from table C for the table A and B to my declared table @keyColumns.

Then I would use a while loop to go through all key columns inside @keyColumns and generate the query and execute it with sp_executesql.

For example:

UPDATE A 
SET ... 
FROM B INNER JOIN A 
ON A.keycol1 = B.keycol1 AND A.keycol2 = B.keycol2 AND ...

Just to make it clear, the C table only specifies key columns for the table B, and from that I know A has the same key columns.

But I want to know if there's a better way to solve this.

Answer 1

Are the key columns held in 'C' the primary key? If so you can retrieve these from INFORMATION_SCHEMA.TABLE_CONSTRAINTS, and INFORMATION_SCHEMA.KEY_COLUMN_USAGE as described here rather than using a different table.

You have to use dynamic SQL for this I think. There is no syntax like FROM B JOIN A ON PRIMARY KEYS. Instead of the WHILE loop though you can just concatenate your query through a SELECT as below.

DECLARE @DynSql nvarchar(max)
DECLARE @TableA sysname
DECLARE @TableB sysname

SET @TableA = 'A'
SET @TableB = 'B';

WITH C AS
(
SELECT 'B' AS [Table], 'keycol2' As col UNION ALL
SELECT 'B' AS [Table], 'keycol1' As col UNION ALL
SELECT 'X' AS [Table], 'keycol1' As col
)

SELECT @DynSql = ISNULL(@DynSql + ' AND ','')+ @TableA + '.'+QUOTENAME(col) + '= ' + @TableB + '.'+QUOTENAME(col)
FROM C WHERE [Table] = @TableB

IF @@ROWCOUNT=0
RAISERROR('No Entry found for table %s',16,1,@TableB)

SET @DynSql = 
'UPDATE ' + @TableA + ' 
SET ... 
FROM ' + @TableB + '  INNER JOIN ' + @TableA + ' ON  
' + @DynSql

PRINT @DynSql