Dynamic SQL for Table Comparision

Induja Rompicherla 1 Reputation point
2020-12-22T19:31:38.737+00:00

Hello experts,

I have two tables.

Table A has exact same column names as table B.

I need my out put like this.

ID(common for both tables) TableA.col1 TableB.col1 TableA.col2 TableB.col2 ...............for all columns

How can i write it in dynamic sql as i have 100 tables to cmpare.

TIA

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2020-12-22T22:18:43.973+00:00

    This requires SQL 2017. If you are on a lower version, you will need to replace string_agg with the FOR XML PATH kludge, which you may be able to find on your own through Google.

    CREATE PROCEDURE compare_tables @tableA nvarchar(260), @tableB nvarchar(260) AS
       SELECT @tableA = quotename(s.name) + '.' + quotename(o.name)
       FROM   sys.objects o
       JOIN   sys.schemas s ON s.schema_id = o.schema_id
       WHERE  o.object_id = object_id(@tableA)
    
       SELECT @tableB = quotename(s.name) + '.' + quotename(o.name)
       FROM   sys.objects o
       JOIN   sys.schemas s ON s.schema_id = o.schema_id
       WHERE  o.object_id = object_id(@tableB)
    
       DECLARE @sql nvarchar(MAX)
    
       SELECT @sql = 'SELECT isnull(A.ID, B.ID) AS ID, ' + 
                             string_agg('A.' + quotename(c.name) + ' AS ' + quotename(c.name + '(A)') + ', ' + 
                                        'B.' + quotename(c.name) + ' AS ' + quotename(c.name + '(B)'), ', ') + '
                      FROM   ' + @tableA + ' AS A 
                      FULL JOIN ' + @tableB + ' AS B ON A.ID = B.ID
                      ORDER BY ID'
       FROM  sys.columns c
       WHERE object_id = object_id(@tableA)
         AND c.name <> 'ID'
    
       PRINT @sql
       EXEC(@sql)
    go
    CREATE TABLE links (ID int NOT NULL, a varchar(20), d date, f float)
    CREATE TABLE rechts (ID int NOT NULL, a varchar(20), d date, f float)
    go
    INSERT links(ID, a, d, f) VALUES(1, 'Albin', '1876-12-12', pi()), (3, 'Tratt', '1964-11-07', 1E34)
    INSERT rechts(ID, a, d, f) VALUES(1, 'Albin', '1876-12-12', sin(0.99)), (2, 'Blygsel', '2012-05-17', 0.009)
    go
    EXEC compare_tables 'links', 'rechts'
    go
    DROP TABLE links, rechts
    DROP PROCEDURE compare_tables
    
    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2020-12-23T02:38:59.237+00:00

    Hi @Induja Rompicherla

    Thank you so much for posting here in Microsoft Q&A.

    Based on Erland's answer, if your SQL Server is 2016 and earlier, you could replace the procedure with below part.

        DECLARE @sql nvarchar(MAX)  
          
        SELECT @sql = 'SELECT isnull(A.ID, B.ID) AS ID, ' +   
    					STUFF((SELECT ', '+'A.' + quotename(d.name) + ' AS ' + quotename(d.name + '(A)') + ', ' +   
                                        'B.' + quotename(d.name) + ' AS ' + quotename(d.name + '(B)')  
    					FROM SYS.COLUMNS d   
    					WHERE c.object_id = d.object_id   
    					AND d.name<>'ID'  
    					FOR XML PATH('')), 1, 1, ''  
    					)				 + '  
                       FROM   ' + @tableA + ' AS A   
                       FULL JOIN ' + @tableB + ' AS B ON A.ID = B.ID  
                       ORDER BY ID'  
        FROM  sys.columns c  
        WHERE object_id = object_id(@tableA)  
          AND c.name <> 'ID'  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

    0 comments No comments