2

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.

Table1 and Table2

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
EzLo
  • 13,780
  • 10
  • 33
  • 38
SZ_SZ
  • 51
  • 1
  • 6
  • Please use text, not images/links, for text. Please give a [mcve]. Please read [ask] & the voting arrow mouseover text. Why did the answers from googling your question not solve your problem? – philipxy Jan 18 '19 at 03:38
  • 1
    Possible duplicate of [Select all columns except one in MySQL?](https://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql) – philipxy Jan 18 '19 at 03:41
  • Are all of your params of type `int`? If so, have you considered whether you've picked the right data structure to start with here? A narrow table with `ID, ParamNumber, ParamValue` may be far simpler to work with in the long run. – Damien_The_Unbeliever Jan 18 '19 at 07:10
  • Your query looks good but you did not include the output you wanted. It's not too clear. – Vijunav Vastivch Jan 18 '19 at 08:41
  • Hi Damien, params are not type of int. It will be of nvarchar. – SZ_SZ Jan 18 '19 at 09:24
  • Hi philipxy, in here, i m trying to get the common parameters set of table 1 and table 2. – SZ_SZ Jan 18 '19 at 09:25
  • Hi Vijunav, I would like to compare 2 tables. Those 2 tables might have same values for all columns except ID. I want to get the common parameters values among that 2 table but ID to reference back to Table1 ID. – SZ_SZ Jan 18 '19 at 09:38
  • What is wrong with typing all column names? – Salman A Jan 18 '19 at 10:58

1 Answers1

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.

sapi
  • 244
  • 1
  • 9
  • Hi Sapi, what if table 1 and table 2 are in separate databases? How does the query be like? – SZ_SZ Jan 21 '19 at 07:06
  • hey, to select tables in a different database just prefix the table name by [databaseName].[schema].[tableName] ex : @TBL1 nvarchar(50) = '[prod].dbo.data' .Now if on database is on another server its a bit trickier you will have to create a linked server in sql server but once you do the syntax is identical : [serverName].[dbName].[schemaName].[tableName] – sapi Jan 21 '19 at 07:21