INNER JOIN with ON All columns except one Column

Question

I have 2 tables(Table1 and Table2). Both tables schema are exactly the same and both might have duplicated set of records except IDs since ID is auto generated.

I would like to get the common set of records but with ID to follow as Table1's ID. So, I query using Inner join. It works as I expected.

SELECT Table1.ID, Table1.Param1, Table1.Param2, Table1.Param3
INTO #Common
FROM Table1
INNER JOIN Table2 ON Table1.Param1 = Table2.Param1
  AND Table1.Param2 = Table2.Param2
  AND Table1.Param3 = Table2.Param3

However, in actual usage, the total number of parameters in both tables will be around 100. So, the total number of comparison inside ON clause will increase up to 100. How can I do inner join by excluding one column instead of comparing all columns in ON clause?

By removing ID column from both tables and doing intersect also no possible since I still want to extract Table1 ID for other purpose. I can achieve the common of 2 table by removing ID and compare those 2 table. However, that still do not serve my requirement, since I need to get Table1 ID for those common data.

SELECT * INTO #TemporaryTable1 FROM Table1
ALTER TABLE #TemporaryTable1 DROP COLUMN ID

SELECT * INTO #TemporaryTable2 FROM Table2
ALTER TABLE #TemporaryTable2 DROP COLUMN ID

SELECT * INTO #Common FROM (SELECT * FROM #TemporaryTable1 INTERSECT SELECT * FROM #TemporaryTable2) data
SELECT * FROM #Common

Answer 1

If i understood your problem correctly i guess you could generate dynamically the query you want to use using the following code :

DECLARE @SQL nvarchar(max) = 'SELECT  ',
    @TBL1 nvarchar(50) = 'data',
    @TBL2 nvarchar(50) = 'data1',
    @EXCLUDEDCOLUMNS nvarchar(100)= 'ID,col1'

-- column selection
SELECT @sql += @tbl1 + '.' + COLUMN_NAME + ' ,
        '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBL1

-- from clause and remove last ,

set @SQL = LEFT(@sql,LEN(@sql) - 5)


SET @sql += '
FROM ' + @TBL1 + ' INNER JOIN
     ' + @TBL2 + '
  ON '

-- define the on clause
SELECt @SQL  += @tbl1 + '.' + COLUMN_NAME + ' = '+ @tbl2 + '.' + COLUMN_NAME +',
     '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TBL1
  AND COLUMN_NAME not in (@EXCLUDEDCOLUMNS)

--remove last ,

set @SQL = LEFT(@sql,LEN(@sql) - 3)

--SELECt @SQL
EXEC SP_EXECUTESQL @sql 

Before you execute make sure the @sql is properly generated. choose the columns you want to exclude from your on clause using the @EXCLUDEDCOLUMNS parameter.