none
Compare two tables using sql query

    Question

  • Hi,

    I have a table with below values

    Table 1

    C1 C2 C3

    1 2 3

    4 5 6

    Table 2

    C1 C2 C3

    1 2 7

    4 8 6

    I need to compare two tables and display only mismatch columns

    Example: Output

    C2 C3

    2 7

    8 6

    Can you pls help me in this regard.

    Monday, October 29, 2012 10:40 AM

Answers

  • Are you talking about my dynamic script? There is a solution for this problem - we can convert all column values to nvarchar(max) first - most common types can be converted to nvarchar(max) and only some exotic types can not be converted.

    Here is the script using nvarchar(max):

    CREATE TABLE Test1(id INT,col2 INT,col3 INT, col4 int, col5 varchar(10), col6 date)
    CREATE TABLE Test2(id INT,col2 INT,col3 INT, col4 int, col5 varchar(10), col6 date)
    
    
    INSERT INTO Test1 VALUES (1,2,3,9,'q', '20120101'),(4,5,6,10,'z','20120404')
    INSERT INTO Test2 VALUES (1,2,7,8, 'm','19990101'),(4,8,6,12, 'dd','20110505')
    
    --------------------------------------------------
     
     declare @sql nvarchar(max), @Cols nvarchar(max)
     
     select @Cols = stuff((select ',(cast(' + QUOTENAME(column_name) + ' as nvarchar(max)),' + 
     QUOTENAME(column_name,'''') + ')' from Information_schema.columns C
     where C.TABLE_SCHEMA = 'dbo' and c.TABLE_NAME = 'Test1' and c.COLUMN_NAME <> 'id'
     order by c.COLUMN_NAME for XML path('')),1,1,'')
     
     set @sql = 'SELECT t1.c as [Column Name] , t1.col as [Expected Value], t2.col as [Actual Value] 
    FROM (select id, c, col from Test1 cross apply (values' + @Cols + ' ) d(col, c)  ) t1
    INNER JOIN 
      (select id, c, col from Test2 cross apply (values' + @Cols+ ' ) d(col, c)  ) t2 
     ON t1.id=t2.id AND t1.c=t2.c
     WHERE t1.col<>t2.col
     Order by t1.c'
     
     print @sql
     
     execute (@sql)
     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, October 30, 2012 1:37 PM
    Moderator
  • You're excluding ClientId column but trying to join columns based on Id. Also, you no longer will be able to join based on the column name. Perhaps you also need to use Ordinal_position and use it for joining instead of the column name (which is not going to match anymore).

    CREATE TABLE #Test1(Clientid INT,col2 INT,col3 VARCHAR(MAX), col4 INT)
    
    CREATE TABLE #Test2(Clientid INT,col2_col INT,col3 VARCHAR(MAX), col4 INT)
    
    INSERT INTO #Test1 VALUES (1,2,'Latha',9),(4,5,'Sandhya',10)
    
    INSERT INTO #Test2 VALUES (1,5,'Rani',8),(4,8,'Sam',12)
    
    --------------------------------------------------
    
     DECLARE @sql NVARCHAR(MAX), @ColS NVARCHAR(MAX), @ColD NVARCHAR(MAX)
    
     SELECT @ColS = STUFF((SELECT ', (CAST(' + QUOTENAME(column_name) + ' AS NVARCHAR(MAX)), ' + CAST(C.Ordinal_Position AS VARCHAR(10)) + ', ' +
    
          QUOTENAME(column_name,'''') + ')' FROM tempdb.INFORMATION_SCHEMA.COLUMNS C
    
          WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME LIKE '%#Test1%' AND c.COLUMN_NAME <> 'ClientID'
    
          ORDER BY C.COLUMN_NAME FOR XML PATH('')),1,1,'')
    
    
    
     SELECT @Cold = STUFF((SELECT ', (CAST(' + QUOTENAME(column_name) + ' AS NVARCHAR(MAX)),' + CAST(C.Ordinal_Position AS VARCHAR(10)) + ', ' + 
    
          QUOTENAME(column_name,'''') + ')' FROM tempdb.INFORMATION_SCHEMA.COLUMNS C
    
          WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME LIKE '%#Test2%' AND c.COLUMN_NAME <> 'ClientID'
    
          ORDER BY C.COLUMN_NAME FOR XML PATH('')),1,1,'')
    
    
     SET @sql = 'SELECT t1.colName as [Column Name],
     t2.ColName as [Second Column Name],
      t1.col as [Expected Value], t2.col as [Actual Value]
    
    FROM (select Clientid, colName, col, OrdPos from #Test1 cross apply (values' + @Cols + ' ) d(col, OrdPos, colName)  ) t1
    
    INNER JOIN
    
      (select Clientid, colName, col, OrdPos from #Test2 cross apply (values' + @Cold+ ' ) d(col, OrdPos, colName)  ) t2
    
     ON t1.Clientid=t2.Clientid AND t1.OrdPos=t2.OrdPos
    
    WHERE t1.col<>t2.col
    
    Order by t1.colName'
    
     PRINT @sql
    
     EXECUTE (@sql)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, November 05, 2012 3:53 PM
    Moderator

All replies

  • SELECT T1.*, T2.*
       FROM T1
            FULL OUTER JOIN
            T2
            ON T1.c1 = T2.c1
               AND T1.c2 = T2.c2
                ...
               AND T1.cn = T2.cn
     WHERE T1.key IS NULL 
        OR T2.key IS NULL; 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Monday, October 29, 2012 10:42 AM
    Answerer
  • Method1

    DECLARE @t1 as table (c1 int,c2 int,c3 int)
    DECLARE @t2 as table (c1 int,c2 int,c3 int)
    INSERT into @t1 values(1,2,3),(4,5,6)
    INSERT into @t2 values(1,2,7),(4,8,6)
    SELECT t2.* from @t1 t full OUTER join @t2 t2 ON t.c1 = t2.c1
    WHERE t.c1  IS NULL OR t2.c1 IS NULL 
    OR BINARY_CHECKSUM(t.c1,t.c2,t.c3) <> BINARY_CHECKSUM(t2.c1,t2.c2,t2.c3)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 29, 2012 11:01 AM
  • Method 2

    DECLARE @t1 as table (c1 int,c2 int,c3 int)
    DECLARE @t2 as table (c1 int,c2 int,c3 int)
    INSERT into @t1 values(1,2,3),(4,5,6)
    INSERT into @t2 values(1,2,7),(4,8,6)
    SELECT t2.* from @t1 t full OUTER join @t2 t2 ON t.c1 = t2.c1 AND t.c2 = t2.c2 AND t.c3 = t2.c3
    WHERE t.c1  IS NULL OR t2.c1 IS NULL  


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 29, 2012 11:04 AM
  • Method 3

    DECLARE @t1 as table (c1 int,c2 int,c3 int)
    DECLARE @t2 as table (c1 int,c2 int,c3 int)
    DECLARE @t3 as table (c1 int,binaryc int)
    INSERT into @t1 values(1,2,3),(4,5,6)
    INSERT into @t2 values(1,2,7),(4,8,6)
     
    INSERT into @t3 
    SELECT c1,BINARY_CHECKSUM(c1,c2,c3) from @t2
    except
     SELECT c1,BINARY_CHECKSUM(c1,c2,c3) from @t1  
    SELECT c2,c3   FROM @t2 WHERE [@t2].c1 IN (Select c1 FROM @t3 t)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 29, 2012 11:17 AM
  • Hi

    Thanks for the response,

    If I am correct the queries mentioned above display all columns.

    Is there any way, where in I can display only columns which has mismatched data.

    Monday, October 29, 2012 11:28 AM
  • Hi anil,

    I think you want to diaplay the column which only mismatched in left or right table , then specify corresponding left or right table columns in the select

    else if you need to display only the columns which are mismatched then you should write dynamic script it is complex one but achivable.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 29, 2012 12:09 PM
  • Assuming that the first column is the Id column:

    select T2.C2, T2.C3

    from T1 INNER JOIN T2 on T1.C1 = T2.C1

    where NOT (T1.C2 = T2.C2 AND T1.C3 = T2.C3)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, October 29, 2012 1:04 PM
    Moderator
  • Thanks for the reply,

    In above queries, I am explicitly mentioning the column names to be displayed like select T2.C2, T2.C3

    I am looking for a query to compare values of two table and display mismatched values dynamically.

    Example

    Table 1(Source)

    C1 C2 C3

    1 2 3

    4 5 6

    Table 2(Destination)

    C1 C2 C3

    1 2 7

    4 8 6

    Expected output:

    Column Name   Expected Value  Actual Value

    C2                    5                      8 

    C3                    3                      7

    Thanks in advance....

    Monday, October 29, 2012 2:45 PM
  • Hi Anil,

    This is however, a Dynamic Query using Cursors, and is always considered to be Resource intensive, degrading Query performance. But, keeping your requirement in mind, this seems to be a feasible approach...

    KEY_COL is one column that I have added to your table (above questions). This is required, to maintain the constraint, that you are matching the correct rows. This is expected to be a Primary key on Both tables.

    CREATE TABLE T1 (KEY_COL INT, C1 INT,C2 INT,C3 INT)
    CREATE TABLE T2 (KEY_COL INT,C1 INT,C2 INT,C3 INT)
    CREATE TABLE MISMATCH_COLUMNS (COLUMN_NAME NVARCHAR(25))  --This table will contain your Mismatched Columns at the end.


    INSERT INTO T1 VALUES(1,1,2,3),(2,4,5,6),(3,6,2,6)
    INSERT INTO T2 VALUES(1,1,2,7),(2,4,8,6),(3,6,2,6)

    DECLARE CRS CURSOR 
    FOR SELECT NAME FROM SYS.COLUMNS WHERE [OBJECT_ID] = OBJECT_ID('T1')

    DECLARE @COL_NAME NVARCHAR(25)
    DECLARE @QUERY NVARCHAR(255)
    DECLARE @FLAG INT = 0
    OPEN CRS
    FETCH NEXT FROM CRS INTO @COL_NAME
    WHILE @@FETCH_STATUS = 0
        BEGIN
    SET @QUERY = 'IF EXISTS(
    SELECT 1 
    FROM T1 
    LEFT OUTER JOIN T2 
    ON T1.KEY_COL = T2.KEY_COL 
    AND T1.['+@COL_NAME+'] = T2.['+@COL_NAME+'] 
    WHERE T2.['+@COL_NAME+'] IS NULL) INSERT INTO Mismatch_columns(COLUMN_NAME) VALUES ('''+@COL_NAME+''')'  
            EXEC (@QUERY)       
            FETCH NEXT FROM CRS INTO @COL_NAME
        END

    CLOSE CRS

    DEALLOCATE CRS

    SELECT COLUMN_NAME as [MISMATCHING COLUMN] FROM MISMATCH_COLUMNS

    Please mark it as an answer,if so.. :)


    Ashu_Blueray


    • Edited by Ashu_Blueray Monday, October 29, 2012 2:58 PM KEY_COL Description
    Monday, October 29, 2012 2:56 PM
  • Are you always matching tables by the first column? If so,

    select  'C2' as ColumName,  T1.C2 as [Expected Value], T2.C2 as [Actual Value] from

    T1 inner join T2 on T1.C1 = T2.C1 and T1.C2 <> T2.C2

    UNION ALL

    select  'C3' as ColumName,  T1.C3 as [Expected Value], T2.C3 as [Actual Value] from

    T1 inner join T2 on T1.C1 = T2.C1 and T1.C3 <> T2.C3

    We can easily generate the above dynamically, if it needs to be.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, October 29, 2012 2:58 PM
    Moderator
  • CREATE TABLE #Test1(  col1 INT,col2 INT,col3 INT)
    CREATE TABLE #Test2(  col1 INT,col2 INT,col3 INT)
    
    
    INSERT INTO #Test1 VALUES (1,2,3),(4,5,6)
    INSERT INTO #Test2 VALUES (1,2,7),(4,8,6)
    
    
    SELECT t1.c as [Column Name] , t1.col as [Expected Value], t2.col as [Actual Value] 
    FROM (select col1, c, col from #Test1 cross apply (values(col1, 'c1'),(col2, 'c2'),(col3, 'c3') ) d(col, c)  ) t1
    INNER JOIN 
      (select col1, c, col from #Test2 cross apply (values(col1, 'c1'),(col2, 'c2'),(col3, 'c3') ) d(col, c)  ) t2
     ON t1.col1=t2.col1 AND t1.c=t2.c
     WHERE t1.col<>t2.col
     Order by t1.c
    
    --Noami's solution
    select  'C2' as ColumName,  T1.col2 as [Expected Value], T2.col2 as [Actual Value] from
    #Test1 t1 inner join #Test2 t2 on T1.col1 = T2.col1 and T1.col2 <> T2.col2
    UNION ALL
    select  'C3' as ColumName,  T1.col3 as [Expected Value], T2.col3 as [Actual Value] from
    #Test1 t1 inner join #Test2  t2 on T1.col1 = T2.col1 and T1.col3 <> T2.col3
    
    drop table #Test1
    drop table #Test2

    Monday, October 29, 2012 6:21 PM
    Moderator
  • Thanks Noami

    Your solution almost solves my problem.

    actually, I need to compare  table that contains more than 50 columns,

    with your suggestion, I may have to write select query for each column like C2, C3. Is there any optimized way to cover 50 or more columns, like using a loop and executing select script.

    Tuesday, October 30, 2012 2:13 AM
  •  

    (SELECT * FROM T1

    UNION

    SELECT * FROM T2)

    EXCEPT

    (SELECT * FROM T1

    INTERSECT

    SELECT * FROM T2)


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, October 30, 2012 2:42 AM
  • Here is using Jingyang idea and dynamic SQL:

    use AllTests
    
    CREATE TABLE Test1(id INT,col2 INT,col3 INT, col4 int)
    CREATE TABLE Test2(id INT,col2 INT,col3 INT, col4 int)
    
    
    INSERT INTO Test1 VALUES (1,2,3,9),(4,5,6,10)
    INSERT INTO Test2 VALUES (1,2,7,8),(4,8,6,12)
    
    --------------------------------------------------
     
     declare @sql nvarchar(max), @Cols nvarchar(max)
     
     select @Cols = stuff((select ',(' + QUOTENAME(column_name) + ',' + 
     QUOTENAME(column_name,'''') + ')' from Information_schema.columns C
     where C.TABLE_SCHEMA = 'dbo' and c.TABLE_NAME = 'Test1' and c.COLUMN_NAME <> 'id'
     order by c.COLUMN_NAME for XML path('')),1,1,'')
     
     set @sql = 'SELECT t1.c as [Column Name] , t1.col as [Expected Value], t2.col as [Actual Value] 
    FROM (select id, c, col from Test1 cross apply (values' + @Cols + ' ) d(col, c)  ) t1
    INNER JOIN 
      (select id, c, col from Test2 cross apply (values' + @Cols+ ' ) d(col, c)  ) t2 
     ON t1.id=t2.id AND t1.c=t2.c
     WHERE t1.col<>t2.col
     Order by t1.c'
     
     print @sql
     
     execute (@sql)
     

    I assume that the tables have the same structure and that the column we use for joining is known in advance and it's Id and all other columns need to be used for comparison and they are all of the same type.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, October 30, 2012 2:47 AM
    Moderator
  • Hi,

    See the below example to identify your requirment :

    select *
    from ( select *
         
    from DB1.dbo.Table
         
    except
         
    select *
         
    from DB2.dbo.Table
        
    ) as T
    union all
    select *
    from (
         
    select *
         
    from DB2.dbo.Table
         
    except
         
    select *
         
    from DB1.dbo.Table
        
    ) as T


    Ahsan KabirAhsan Kabir
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you.
    This can be beneficial to other community members reading the thread.
    http://www.aktechforum.blogspot.com/


    Tuesday, October 30, 2012 6:13 AM
  • The script is failing if the columns in the table have different data types, working only if all the columns have same data types.

    By default Expected Value and Actual Value columns are accepting only integer values if we provide any string values for any one of the columns its throwing following error when executing below modified script

    Is there any way to execute below script successfully.

    Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the varchar value 'Latha' to data type int.

    Tuesday, October 30, 2012 10:17 AM
  • Are you talking about my dynamic script? There is a solution for this problem - we can convert all column values to nvarchar(max) first - most common types can be converted to nvarchar(max) and only some exotic types can not be converted.

    Here is the script using nvarchar(max):

    CREATE TABLE Test1(id INT,col2 INT,col3 INT, col4 int, col5 varchar(10), col6 date)
    CREATE TABLE Test2(id INT,col2 INT,col3 INT, col4 int, col5 varchar(10), col6 date)
    
    
    INSERT INTO Test1 VALUES (1,2,3,9,'q', '20120101'),(4,5,6,10,'z','20120404')
    INSERT INTO Test2 VALUES (1,2,7,8, 'm','19990101'),(4,8,6,12, 'dd','20110505')
    
    --------------------------------------------------
     
     declare @sql nvarchar(max), @Cols nvarchar(max)
     
     select @Cols = stuff((select ',(cast(' + QUOTENAME(column_name) + ' as nvarchar(max)),' + 
     QUOTENAME(column_name,'''') + ')' from Information_schema.columns C
     where C.TABLE_SCHEMA = 'dbo' and c.TABLE_NAME = 'Test1' and c.COLUMN_NAME <> 'id'
     order by c.COLUMN_NAME for XML path('')),1,1,'')
     
     set @sql = 'SELECT t1.c as [Column Name] , t1.col as [Expected Value], t2.col as [Actual Value] 
    FROM (select id, c, col from Test1 cross apply (values' + @Cols + ' ) d(col, c)  ) t1
    INNER JOIN 
      (select id, c, col from Test2 cross apply (values' + @Cols+ ' ) d(col, c)  ) t2 
     ON t1.id=t2.id AND t1.c=t2.c
     WHERE t1.col<>t2.col
     Order by t1.c'
     
     print @sql
     
     execute (@sql)
     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, October 30, 2012 1:37 PM
    Moderator
  • Before investing too much more time on this.... How sure are you that you properly understand your own requirements? 

    For example, even in your sample data and sample output, when column C was different but column B was the same, you listed column B as well as column C.  When Column B was different but column C was the same, you listed column C as well as column B. 

    I see two sides to the issue...

    1. side one that Column B is listed because some value somewhere else in the output had a difference for column B.  (But... that opens up  another question.. how will whoever uses this information know whether a specific row/column is there because THAT row/column mismatched, vs. because some OTHER row for that column had a mismatch?)
    2. or, (to me more likely) that there's some misunderstanding of the requirement to begin with. 

    This is a somewhat unusual output request.  What will the output be used for? 

    Tuesday, October 30, 2012 6:38 PM
  • if you get the source and target mappings - table names, column names and data types into a temp table, you could check the data type while generating the dynamic sql and append quotes for string type columns

    Wednesday, October 31, 2012 6:39 AM
  • Check this out:

    Using dynamic sql and Common table expression you can achieve:

    Expected output:

    Column Name   Expected Value  Actual Value

    C2                    5                      8 

    C3                    3                      7

    Thanks in advance....

    CREATE TABLE SOURCE (ROWNBR INT, C1 INT, C2 INT, C3 INT)
    CREATE TABLE DEST (ROWNBR INT, C1 INT, C2 INT, C3 INT)
    ---------- 
    INSERT INTO SOURCE VALUES(1,0,2,3)
    INSERT INTO SOURCE VALUES(2,4,5,6)
    INSERT INTO DEST VALUES(1,1,2,7)
    INSERT INTO DEST VALUES(2,4,8,6)
    ---------- 
    DECLARE @COLNAME VARCHAR(100)
    DECLARE @SQL VARCHAR(max)
    SET @SQL = ''
    DECLARE COL_CUR CURSOR  -- HOLDS COLUMN NAMES C1, C2 etc
    FOR SELECT [NAME] FROM SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('SOURCE') AND [NAME] <> 'ROWNBR'
    OPEN COL_CUR
    FETCH NEXT FROM COL_CUR INTO @COLNAME
    WHILE @@FETCH_STATUS = 0
    BEGIN 
    SET @SQL = @SQL + (SELECT 'SELECT ''' + @COLNAME + ''' AS COLUMN_NAME, CASE WHEN CONVERT(VARCHAR(10),SOURCE.' + @COLNAME + ') <> CONVERT(VARCHAR(10),DEST.' + @COLNAME + ') THEN CONVERT(VARCHAR(10),SOURCE.' + @COLNAME + ') END AS [EXPECTED VALUE], DEST.' + @COLNAME + ' AS [ACTUAL VALUE] FROM SOURCE INNER JOIN DEST ON SOURCE.ROWNBR = DEST.ROWNBR UNION ALL ')
    FETCH NEXT FROM COL_CUR INTO @COLNAME
    END
    CLOSE COL_CUR
    DEALLOCATE COL_CUR
    SET @SQL = (SELECT REPLACE(@SQL + '   ',' UNION ALL    ',''))
    SET @SQL = ';WITH CTE (TABLE_NAME, [EXPECTED_VALUE], [ACTUAL_VALUE]) AS (' + @SQL + ')SELECT * FROM CTE WHERE [EXPECTED_VALUE] IS NOT NULL'
    PRINT @SQL
    ------------
    EXEC (@SQL)
    ------------
    DROP TABLE SOURCE 
    DROP TABLE DEST 


    • Edited by NeverHopeless Wednesday, October 31, 2012 1:43 PM added reference of comment
    Wednesday, October 31, 2012 1:13 PM
  • HI,

    Thanks for your response, it almost meets my requirement.

    Only one concern

    The script is failing if the columns in the table have different columns names, working only if all the columns have same names in two tables.

    For example the column name in first table have name as LastName and second table have LastName_Client. When we execute the script the mismatched column name records are not coming even though data mismatch is there in both the columns. I have two tables in different databases.

    In below example the col2 value is not coming even though data mismatch is there in both the columns.

    Is there any way to execute below script successfully.

    CREATE TABLE #Test1(id INT,col2 INT,col3 VARCHAR(MAX), col4 int)

    CREATE TABLE #Test2(id INT,col2_col INT,col3 VARCHAR(MAX), col4 int)

    INSERT INTO Test1 VALUES (1,2,'Latha',9),(4,5,'Sandhya',10)

    INSERT INTO Test2 VALUES (1,5,'Rani',8),(4,8,'Sam',12)

    --------------------------------------------------

     declare @sql nvarchar(max), @Cols nvarchar(max)

     SELECT @ColS = STUFF((SELECT ',(CAST(' + QUOTENAME(column_name) + ' AS NVARCHAR(MAX)),' +

          QUOTENAME(column_name,'''') + ')' FROM tempdb.INFORMATION_SCHEMA.COLUMNS C

          WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME LIKE '%#Test1%' AND c.COLUMN_NAME <> 'ClientID'

          ORDER BY C.COLUMN_NAME FOR XML PATH('')),1,1,'')

          print @ColS

     SELECT @Cold = STUFF((SELECT ',(CAST(' + QUOTENAME(column_name) + ' AS NVARCHAR(MAX)),' +

          QUOTENAME(column_name,'''') + ')' FROM tempdb.INFORMATION_SCHEMA.COLUMNS C

          WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME LIKE '%#Test2%' AND c.COLUMN_NAME <> 'ClientID'

          ORDER BY C.COLUMN_NAME FOR XML PATH('')),1,1,'')

          print @ColS

     set @sql = 'SELECT t1.c as [Column Name] , t1.col as [Expected Value], t2.col as [Actual Value]

    FROM (select id, c, col from Test1 cross apply (values' + @Cols + ' ) d(col, c)  ) t1

    INNER JOIN

      (select id, c, col from Test2 cross apply (values' + @Cold+ ' ) d(col, c)  ) t2

     ON t1.id=t2.id AND t1.c=t2.c

    WHERE t1.col<>t2.col

    Order by t1.c'

     print @sql

     execute (@sql)

    Thanks in advance

    Monday, November 05, 2012 3:01 PM
  • You're excluding ClientId column but trying to join columns based on Id. Also, you no longer will be able to join based on the column name. Perhaps you also need to use Ordinal_position and use it for joining instead of the column name (which is not going to match anymore).

    CREATE TABLE #Test1(Clientid INT,col2 INT,col3 VARCHAR(MAX), col4 INT)
    
    CREATE TABLE #Test2(Clientid INT,col2_col INT,col3 VARCHAR(MAX), col4 INT)
    
    INSERT INTO #Test1 VALUES (1,2,'Latha',9),(4,5,'Sandhya',10)
    
    INSERT INTO #Test2 VALUES (1,5,'Rani',8),(4,8,'Sam',12)
    
    --------------------------------------------------
    
     DECLARE @sql NVARCHAR(MAX), @ColS NVARCHAR(MAX), @ColD NVARCHAR(MAX)
    
     SELECT @ColS = STUFF((SELECT ', (CAST(' + QUOTENAME(column_name) + ' AS NVARCHAR(MAX)), ' + CAST(C.Ordinal_Position AS VARCHAR(10)) + ', ' +
    
          QUOTENAME(column_name,'''') + ')' FROM tempdb.INFORMATION_SCHEMA.COLUMNS C
    
          WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME LIKE '%#Test1%' AND c.COLUMN_NAME <> 'ClientID'
    
          ORDER BY C.COLUMN_NAME FOR XML PATH('')),1,1,'')
    
    
    
     SELECT @Cold = STUFF((SELECT ', (CAST(' + QUOTENAME(column_name) + ' AS NVARCHAR(MAX)),' + CAST(C.Ordinal_Position AS VARCHAR(10)) + ', ' + 
    
          QUOTENAME(column_name,'''') + ')' FROM tempdb.INFORMATION_SCHEMA.COLUMNS C
    
          WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME LIKE '%#Test2%' AND c.COLUMN_NAME <> 'ClientID'
    
          ORDER BY C.COLUMN_NAME FOR XML PATH('')),1,1,'')
    
    
     SET @sql = 'SELECT t1.colName as [Column Name],
     t2.ColName as [Second Column Name],
      t1.col as [Expected Value], t2.col as [Actual Value]
    
    FROM (select Clientid, colName, col, OrdPos from #Test1 cross apply (values' + @Cols + ' ) d(col, OrdPos, colName)  ) t1
    
    INNER JOIN
    
      (select Clientid, colName, col, OrdPos from #Test2 cross apply (values' + @Cold+ ' ) d(col, OrdPos, colName)  ) t2
    
     ON t1.Clientid=t2.Clientid AND t1.OrdPos=t2.OrdPos
    
    WHERE t1.col<>t2.col
    
    Order by t1.colName'
    
     PRINT @sql
    
     EXECUTE (@sql)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, November 05, 2012 3:53 PM
    Moderator
  • @Anil, Have you checked my post ? It returns the output in the same way you are expecting...
    Monday, November 05, 2012 4:41 PM
  • Hi ,

    In the earlier post, it was mentioned that using cursor will impact the performance.

    we have requirement, where in we need compare some 10-20 lakh records.

    Any idea, how much time does it take to validate 10-20 lakh records..

    Thanks,

    anil kumar.

    Tuesday, November 06, 2012 5:22 AM
  • Hi ,

    In the earlier post, it was mentioned that using cursor will impact the performance.

    we have requirement, where in we need compare some 10-20 lakh records.

    Any idea, how much time does it take to validate 10-20 lakh records..

    Thanks,

    anil kumar.

    Anil, does Naomi's last post answer your question?

    Thanks!


    Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Twitter, Wiki)

    Tuesday, November 06, 2012 6:12 PM
    Owner
  • Hi,               

        ALGORITHM:                

    1. pivot the columns to rows
    2. apply inner join  using all values of the columns formed after pivoting
    3. this would give the matching rows and can be eliminated using where not exists
    4. the resulting rows would be the columns which vary
    5. pivot the rows to columns

    Regards.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help , or you may vote-up a helpful post

    Wednesday, November 07, 2012 2:24 PM
  • Hi ,

    Thank you very much for your valuable response. it has solved most of my problems

    One small issue I am facing.

    The script is not comparing NULL with any value. If there a NULL in one of the column in source data base and some value in Destination database, in this case the script is just ignoring the comparison and not returning the mismatches.

    Is there any way to overcome the issue.

    For example:

    DECLARE @sql NVARCHAR(MAX),@ColS NVARCHAR(MAX),@ColT NVARCHAR(MAX),@column NVARCHAR(MAX)

         

          -- Sroting source columns into variable @Cols

          SELECT @ColS = STUFF((SELECT ',(CAST(' + QUOTENAME(column_name) + ' AS NVARCHAR(MAX)),' + CAST(C.Ordinal_Position AS VARCHAR(10)) + ', '+

          QUOTENAME(column_name,'''') + ')' FROM tempdb.INFORMATION_SCHEMA.COLUMNS C

          WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME LIKE '%#Table1%' AND c.COLUMN_NAME <> 'ClientID'

          ORDER BY C.COLUMN_NAME FOR XML PATH('')),1,1,'')

         

          PRINT @ColS

         

          -- Sroting destination columns into variable @Cold

          SELECT @ColT = STUFF((SELECT ',(CAST(' + QUOTENAME(column_name) + ' AS NVARCHAR(MAX)),' +  CAST(C.Ordinal_Position AS VARCHAR(10)) + ', '+

          QUOTENAME(column_name,'''') + ')' FROM tempdb.INFORMATION_SCHEMA.COLUMNS C

          WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME LIKE '%#Table2%' AND c.COLUMN_NAME <> '[Beneficiary ID / File ID*]'

          ORDER BY C.COLUMN_NAME FOR XML PATH('')),1,1,'')

         

          PRINT @ColT

         

          SET @sql = 'SELECT t1.ClientID,t1.ColName as [Source_ColumnName],t2.ColName as [Target ColumnName], t1.col as [Expected Value], t2.col as [Actual Value]

          FROM (select ClientID , colName, col, OrdPos from #Table1 cross apply (values' + @ColS + ' ) d(col, OrdPos, colName)  ) t1

          INNER JOIN

          (select [Beneficiary ID / File ID*], colName, col, OrdPos from #Table2 cross apply (values' + @ColT+ ' ) d(col, OrdPos, colName)  ) t2

          ON t1.ClientID =t2.[Beneficiary ID / File ID*] AND t1.OrdPos=t2.OrdPos

          WHERE t1.col<>t2.col

          ORDER BY t1.clientid'

         

    PRINT @sql

    EXECUTE (@sql)

    Friday, February 22, 2013 4:49 AM
  • You can do this minor change:

    SET @sql = 'SELECT t1.ClientID,t1.ColName as [Source_ColumnName],t2.ColName as [Target ColumnName], t1.col as [Expected Value], t2.col as [Actual Value]
    
          FROM (select ClientID , colName, col, OrdPos from #Table1 cross apply (values' + @ColS + ' ) d(col, OrdPos, colName)  ) t1
    
          INNER JOIN
    
          (select [Beneficiary ID / File ID*], colName, col, OrdPos from #Table2 cross apply (values' + @ColT+ ' ) d(col, OrdPos, colName)  ) t2
    
          ON t1.ClientID =t2.[Beneficiary ID / File ID*] AND t1.OrdPos=t2.OrdPos
    
          WHERE t1.col<>t2.col OR (t1.Col IS NULL and t2.Col IS NOT NULL) OR (t1.col IS NOT NULL and t2.Col IS NULL)
    
          ORDER BY t1.clientid'
    
         
    
    PRINT @sql
    
    EXECUTE (@sql)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, February 22, 2013 3:42 PM
    Moderator