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.