none
Compare two tables with Data using T-SQL RRS feed

  • Question

  • Hello Experts,

    Before I start, I am only looking for a SQL based solution instead of any other tools as we will not be able to invest in the tools that are out in the market. Also, we would like to call a Stored Procedure from the Application UX so that it generates the report for us. There are two databases on our SQL box which gets data from two different sources (but the data is same most of the time). The columns on both the tables are same. But, since both of them get populated through different sources, we would like to know the differences in data between both the tables for each column.

    The actual problem:

    What I am looking for is, when I pass the table name, it should pull the columns for each table (or one table) from the metadata dynamically first. Then, a temp table should be created dynamically based on that with an additional flag for each column. Finally, compare each column in both the tables and populate the flag (Y or N) accordingly in the temp table.

    The reason for doing this way is, every time a user passes a table of their choice, each result would be different.

    Here is some sample DDL/DML for your reference:

    CREATE TABLE dbo.E_App(
    E_ID INT NOT NULL PRIMARY KEY,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY NUMERIC(19,2)
    );
    
    CREATE TABLE dbo.E_Legacy(
    E_ID INT NOT NULL PRIMARY KEY,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY DECIMAL(19,2)
    );
    
    INSERT INTO E_App (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 325.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '', 2313.10),
    (105, 'Ben', 'N 1st Street', NULL);
    
    INSERT INTO E_Legacy (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 825.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '8645 Street', 2313.10),
    (105, 'Jen', 'N 1st Street', 122.00);
    
    SELECT * FROM dbo.E_App;
    SELECT * FROM dbo.E_Legacy;
    Expected Output from the Stored Procedure:

    ExpectedOutput

    Please let me know if you need anything else.

    Thanks!


    Known is a DROP, Unknown is an OCEAN.

    Wednesday, June 12, 2019 1:52 PM

All replies

  • SELECT a.E_ID, b.E_ID
    , Case when ISNULL(a.E_ID,'') =ISNULL(b.E_ID, '') then 'Y' else 'N' End flag1,
    a.E_NM, b.E_NM  
    , Case when ISNULL(a.E_NM,'') =ISNULL(b.E_NM, '') then 'Y' else 'N' End flag2,
    a.E_ADR, b.E_ADR  
    , Case when ISNULL(a.E_ADR,'') =ISNULL(b.E_ADR, '') then 'Y' else 'N' End flag3,
    a.E_SALARY,b.E_SALARY
    , Case when ISNULL(a.E_SALARY,0) =ISNULL(b.E_SALARY, 0) then 'Y' else 'N' End flag4
    FROM dbo.E_App a  join  dbo.E_Legacy b on a.E_ID=b.E_ID

    Wednesday, June 12, 2019 2:31 PM
    Moderator
  • Getting the details :

     SELECT E_App
    .*,E_Legacy
    .*
       FROM E_App
            FULL OUTER JOIN
            E_Legacy
            ON E_App.c1 = E_Legacy.c1
               AND E_App.c2 = E_Legacy.c2
                ...
               AND E_App.cn = E_Legacy.cn
     WHERE E_App.key IS NULL 
        OR E_Legacy.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 Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, June 12, 2019 2:36 PM
    Answerer
  • Getting the details :

     SELECT E_App
    .*,E_Legacy
    .*
       FROM E_App
            FULL OUTER JOIN
            E_Legacy
            ON E_App.c1 = E_Legacy.c1
               AND E_App.c2 = E_Legacy.c2
                ...
               AND E_App.cn = E_Legacy.cn
     WHERE E_App.key IS NULL 
        OR E_Legacy.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 Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thanks Uri! But, this isn't displaying the results I wanted. Instead of returning 5 records with all the values, it is only returning partial values with 6 records.

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, June 12, 2019 2:42 PM
  • SELECT a.E_ID, b.E_ID
    , Case when ISNULL(a.E_ID,'') =ISNULL(b.E_ID, '') then 'Y' else 'N' End flag1,
    a.E_NM, b.E_NM  
    , Case when ISNULL(a.E_NM,'') =ISNULL(b.E_NM, '') then 'Y' else 'N' End flag2,
    a.E_ADR, b.E_ADR  
    , Case when ISNULL(a.E_ADR,'') =ISNULL(b.E_ADR, '') then 'Y' else 'N' End flag3,
    a.E_SALARY,b.E_SALARY
    , Case when ISNULL(a.E_SALARY,0) =ISNULL(b.E_SALARY, 0) then 'Y' else 'N' End flag4
    FROM dbo.E_App a  join  dbo.E_Legacy b on a.E_ID=b.E_ID

    Thanks Jingyang! How do I make this dynamic? Because, I wouldn't know how many columns I would be having in a table when I pass the table name.

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, June 12, 2019 2:43 PM
  • Hi Bangaaram,

     

    Please try following script.

      
    /*IF OBJECT_ID('E_App') IS NOT NULL drop table  E_App
    IF OBJECT_ID('E_Legacy') IS NOT NULL drop table  E_Legacy
    go */
    CREATE TABLE dbo.E_App(
    E_ID INT NOT NULL PRIMARY KEY,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY NUMERIC(19,2)
    );
    
    CREATE TABLE dbo.E_Legacy(
    E_ID INT NOT NULL PRIMARY KEY,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY DECIMAL(19,2)
    );
    
    INSERT INTO E_App (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 325.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '', 2313.10),
    (105, 'Ben', 'N 1st Street', NULL);
    
    INSERT INTO E_Legacy (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 825.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '8645 Street', 2313.10),
    (105, 'Jen', 'N 1st Street', 122.00);
    
    
    declare @sql varchar(max)=''
    declare @i int =1
    
    ;with cte as (
    SELECT distinct QUOTENAME(COLUMN_NAME) as [name] ,count(*)over(partition by COLUMN_NAME) ct 
    FROM INFORMATION_SCHEMA.COLUMNS p2
    WHERE TABLE_NAME in ( 'E_App','E_Legacy'))
    ,cte1 as (
    select [name],row_number()over(order by (select 1)) rn 
    from cte where ct=2
    )
    select @sql=@sql+'
    a.'+[name]+', b.'+[name]+',
    Case when ISNULL(cast(a.'+[name]+' as varchar(10)),'''') =ISNULL(cast(b.'+[name]+' as varchar(10)), '''') then ''Y'' else ''N'' End flag'+cast(rn as varchar(10))+','
    from cte1 
    
    set @sql= 'SELECT '+substring(@sql,1,len(@sql)-1)+' 
    FROM dbo.E_App a  join  dbo.E_Legacy b on a.E_ID=b.E_ID'
    Print (@sql)
    exec (@sql)
    /*
    SELECT 
    	a.[E_ADR], b.[E_ADR],
    	Case when ISNULL(cast(a.[E_ADR] as varchar(10)),'') =ISNULL(cast(b.[E_ADR] as varchar(10)), '') then 'Y' else 'N' End flag1,
    	a.[E_ID], b.[E_ID],
    	Case when ISNULL(cast(a.[E_ID] as varchar(10)),'') =ISNULL(cast(b.[E_ID] as varchar(10)), '') then 'Y' else 'N' End flag2,
    	a.[E_NM], b.[E_NM],
    	Case when ISNULL(cast(a.[E_NM] as varchar(10)),'') =ISNULL(cast(b.[E_NM] as varchar(10)), '') then 'Y' else 'N' End flag3,
    	a.[E_SALARY], b.[E_SALARY],
    	Case when ISNULL(cast(a.[E_SALARY] as varchar(10)),'') =ISNULL(cast(b.[E_SALARY] as varchar(10)), '') then 'Y' else 'N' End flag4 
    FROM dbo.E_App a  join  dbo.E_Legacy b on a.E_ID=b.E_ID
    E_ADR                                              E_ADR                                              flag1 E_ID        E_ID        flag2 E_NM                           E_NM                           flag3 E_SALARY                                E_SALARY                                flag4
    -------------------------------------------------- -------------------------------------------------- ----- ----------- ----------- ----- ------------------------------ ------------------------------ ----- --------------------------------------- --------------------------------------- -----
    123 Blvd                                           123 Blvd                                           Y     101         101         Y     John                           John                           Y     2500.00                                 2500.00                                 Y
    332 Ave                                            332 Ave                                            Y     102         102         Y     Dave                           Dave                           Y     325.59                                  825.59                                  N
    524 Cir                                            524 Cir                                            Y     103         103         Y     Mark                           Mark                           Y     9846.48                                 9846.48                                 Y
                                                       8645 Street                                        N     104         104         Y     Adam                           Adam                           Y     2313.10                                 2313.10                                 Y
    N 1st Street                                       N 1st Street                                       Y     105         105         Y     Ben                            Jen                            N     NULL                                    122.00                                  N
    */
    
    
    

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, June 13, 2019 8:14 AM
  • Hi Bangaaram,

     

    Please try following script.

      
    /*IF OBJECT_ID('E_App') IS NOT NULL drop table  E_App
    IF OBJECT_ID('E_Legacy') IS NOT NULL drop table  E_Legacy
    go */
    CREATE TABLE dbo.E_App(
    E_ID INT NOT NULL PRIMARY KEY,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY NUMERIC(19,2)
    );
    
    CREATE TABLE dbo.E_Legacy(
    E_ID INT NOT NULL PRIMARY KEY,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY DECIMAL(19,2)
    );
    
    INSERT INTO E_App (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 325.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '', 2313.10),
    (105, 'Ben', 'N 1st Street', NULL);
    
    INSERT INTO E_Legacy (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 825.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '8645 Street', 2313.10),
    (105, 'Jen', 'N 1st Street', 122.00);
    
    
    declare @sql varchar(max)=''
    declare @i int =1
    
    ;with cte as (
    SELECT distinct QUOTENAME(COLUMN_NAME) as [name] ,count(*)over(partition by COLUMN_NAME) ct 
    FROM INFORMATION_SCHEMA.COLUMNS p2
    WHERE TABLE_NAME in ( 'E_App','E_Legacy'))
    ,cte1 as (
    select [name],row_number()over(order by (select 1)) rn 
    from cte where ct=2
    )
    select @sql=@sql+'
    a.'+[name]+', b.'+[name]+',
    Case when ISNULL(cast(a.'+[name]+' as varchar(10)),'''') =ISNULL(cast(b.'+[name]+' as varchar(10)), '''') then ''Y'' else ''N'' End flag'+cast(rn as varchar(10))+','
    from cte1 
    
    set @sql= 'SELECT '+substring(@sql,1,len(@sql)-1)+' 
    FROM dbo.E_App a  join  dbo.E_Legacy b on a.E_ID=b.E_ID'
    Print (@sql)
    exec (@sql)
    /*
    SELECT 
    	a.[E_ADR], b.[E_ADR],
    	Case when ISNULL(cast(a.[E_ADR] as varchar(10)),'') =ISNULL(cast(b.[E_ADR] as varchar(10)), '') then 'Y' else 'N' End flag1,
    	a.[E_ID], b.[E_ID],
    	Case when ISNULL(cast(a.[E_ID] as varchar(10)),'') =ISNULL(cast(b.[E_ID] as varchar(10)), '') then 'Y' else 'N' End flag2,
    	a.[E_NM], b.[E_NM],
    	Case when ISNULL(cast(a.[E_NM] as varchar(10)),'') =ISNULL(cast(b.[E_NM] as varchar(10)), '') then 'Y' else 'N' End flag3,
    	a.[E_SALARY], b.[E_SALARY],
    	Case when ISNULL(cast(a.[E_SALARY] as varchar(10)),'') =ISNULL(cast(b.[E_SALARY] as varchar(10)), '') then 'Y' else 'N' End flag4 
    FROM dbo.E_App a  join  dbo.E_Legacy b on a.E_ID=b.E_ID
    E_ADR                                              E_ADR                                              flag1 E_ID        E_ID        flag2 E_NM                           E_NM                           flag3 E_SALARY                                E_SALARY                                flag4
    -------------------------------------------------- -------------------------------------------------- ----- ----------- ----------- ----- ------------------------------ ------------------------------ ----- --------------------------------------- --------------------------------------- -----
    123 Blvd                                           123 Blvd                                           Y     101         101         Y     John                           John                           Y     2500.00                                 2500.00                                 Y
    332 Ave                                            332 Ave                                            Y     102         102         Y     Dave                           Dave                           Y     325.59                                  825.59                                  N
    524 Cir                                            524 Cir                                            Y     103         103         Y     Mark                           Mark                           Y     9846.48                                 9846.48                                 Y
                                                       8645 Street                                        N     104         104         Y     Adam                           Adam                           Y     2313.10                                 2313.10                                 Y
    N 1st Street                                       N 1st Street                                       Y     105         105         Y     Ben                            Jen                            N     NULL                                    122.00                                  N
    */
    
    
    

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thank you Rachel! I see that the PK columns are still being hardcoded in the dynamic SQL. What if I don't know which ones are the PKs in a table? Or, what if there are more than one PK on a table?


    Known is a DROP, Unknown is an OCEAN.

    Tuesday, June 18, 2019 3:46 PM
  • Hi Bangaaram,

     

    Would you like following one ?

    /*IF OBJECT_ID('E_App') IS NOT NULL drop table  E_App
    IF OBJECT_ID('E_Legacy') IS NOT NULL drop table  E_Legacy
    go */
    CREATE TABLE dbo.E_App(
    E_ID INT NOT NULL PRIMARY KEY,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY NUMERIC(19,2)
    );
    
    CREATE TABLE dbo.E_Legacy(
    E_ID INT NOT NULL PRIMARY KEY,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY DECIMAL(19,2)
    );
    
    INSERT INTO E_App (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 325.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '', 2313.10),
    (105, 'Ben', 'N 1st Street', NULL);
    
    INSERT INTO E_Legacy (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 825.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '8645 Street', 2313.10),
    (105, 'Jen', 'N 1st Street', 122.00);
    
    
    
    
    declare @sql varchar(max)=''
    declare @i int =1
    declare @join_colum varchar(max)=''
    
    ;with cte as (
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='E_App' 
    ),cte1 as (
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='E_Legacy' 
    )
    select @join_colum='a.'+a.COLUMN_NAME+'=b.'+b.COLUMN_NAME from cte a join cte1 b on a.COLUMN_NAME=b.COLUMN_NAME
    
    ;with cte as (
    SELECT distinct QUOTENAME(COLUMN_NAME) as [name] ,count(*)over(partition by COLUMN_NAME) ct 
    FROM INFORMATION_SCHEMA.COLUMNS p2
    WHERE TABLE_NAME in ( 'E_App','E_Legacy'))
    ,cte1 as (
    select [name],row_number()over(order by (select 1)) rn 
    from cte where ct=2
    )
    select @sql=@sql+'
    a.'+[name]+', b.'+[name]+',
    Case when ISNULL(cast(a.'+[name]+' as varchar(10)),'''') =ISNULL(cast(b.'+[name]+' as varchar(10)), '''') then ''Y'' else ''N'' End flag'+cast(rn as varchar(10))+','
    from cte1 
    
    set @sql= 'SELECT '+substring(@sql,1,len(@sql)-1)+' 
    FROM dbo.E_App a  join  dbo.E_Legacy b on '+@join_colum
    Print (@sql)
    
    
    exec (@sql)
    /*
    SELECT 
    	a.[E_ADR], b.[E_ADR],
    	Case when ISNULL(cast(a.[E_ADR] as varchar(10)),'') =ISNULL(cast(b.[E_ADR] as varchar(10)), '') then 'Y' else 'N' End flag1,
    	a.[E_ID], b.[E_ID],
    	Case when ISNULL(cast(a.[E_ID] as varchar(10)),'') =ISNULL(cast(b.[E_ID] as varchar(10)), '') then 'Y' else 'N' End flag2,
    	a.[E_NM], b.[E_NM],
    	Case when ISNULL(cast(a.[E_NM] as varchar(10)),'') =ISNULL(cast(b.[E_NM] as varchar(10)), '') then 'Y' else 'N' End flag3,
    	a.[E_SALARY], b.[E_SALARY],
    	Case when ISNULL(cast(a.[E_SALARY] as varchar(10)),'') =ISNULL(cast(b.[E_SALARY] as varchar(10)), '') then 'Y' else 'N' End flag4 
    FROM dbo.E_App a  join  dbo.E_Legacy b on a.E_ID=b.E_ID
    E_ADR                                              E_ADR                                              flag1 E_ID        E_ID        flag2 E_NM                           E_NM                           flag3 E_SALARY                                E_SALARY                                flag4
    -------------------------------------------------- -------------------------------------------------- ----- ----------- ----------- ----- ------------------------------ ------------------------------ ----- --------------------------------------- --------------------------------------- -----
    123 Blvd                                           123 Blvd                                           Y     101         101         Y     John                           John                           Y     2500.00                                 2500.00                                 Y
    332 Ave                                            332 Ave                                            Y     102         102         Y     Dave                           Dave                           Y     325.59                                  825.59                                  N
    524 Cir                                            524 Cir                                            Y     103         103         Y     Mark                           Mark                           Y     9846.48                                 9846.48                                 Y
                                                       8645 Street                                        N     104         104         Y     Adam                           Adam                           Y     2313.10                                 2313.10                                 Y
    N 1st Street                                       N 1st Street                                       Y     105         105         Y     Ben                            Jen                            N     NULL                                    122.00                                  N
    */
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 19, 2019 3:38 AM
  • Hi Rachel,

    Upon trying to change the table names in the above script, I am getting the below error. Looks like the script is only working for those two tables.

    Msg 537, Level 16, State 3, Line 43
    Invalid length parameter passed to the LEFT or SUBSTRING function.



    Known is a DROP, Unknown is an OCEAN.

    Wednesday, June 19, 2019 4:12 PM
  • Hi Bangaaram,

     

    Could you please share us more information about your real table ?

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 20, 2019 8:48 AM
  • Hi Bangaaram,

     

    Could you please share us more information about your real table ?

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi Rachel,

    So, instead of passing the table names "E_App" and "E_Legacy", I tried different table names with different structures. For example, Source Table name is "All_Global_Machinery_Sales" with 20 columns and Target Table name is "Machinery_Domain_Sales" with the same 20 columns and almost similar data.  What I want to do is, no matter what table names I pass, it should return me the similar result set as above with source column, target column and flag. Let me know if you need any other information. Let me know if you need any other information.


    Known is a DROP, Unknown is an OCEAN.

    Friday, June 21, 2019 2:49 PM
  • Hi Bangaaram,

     

    Could you please share us the result of PRINT(@SQL) ?

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 24, 2019 10:02 AM
  • Hi Bangaaram,

     

    Could you please share us the result of PRINT(@SQL) ?

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi Rachel,

    The PRINT(@SQL) isn't returning anything. Looks like the above errors occurs even before.

    Here is what I am passing with the table names mentioned in my previous post. I'm guessing I'm missing something in the second CTE as we are passing tables from two different databases. What do you think?

    ;with cte as (
    SELECT COLUMN_NAME FROM LegacyDB.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @SRC_TBL 
    ),cte1 as (
    SELECT COLUMN_NAME FROM ApplicationDB.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TGT_TBL
    )
    select @join_colum='a.'+a.COLUMN_NAME+'=b.'+b.COLUMN_NAME from cte a join cte1 b on a.COLUMN_NAME=b.COLUMN_NAME
    
    ;with cte as (
    SELECT distinct QUOTENAME(COLUMN_NAME) as [name] ,count(*)over(partition by COLUMN_NAME) ct 
    FROM INFORMATION_SCHEMA.COLUMNS p2
    WHERE TABLE_NAME in ( @SRC_TBL, @TGT_TBL))
    ,cte1 as (
    select [name],row_number()over(order by (select 1)) rn 
    from cte where ct=2
    )
    select @sql=@sql+'
    a.'+[name]+', b.'+[name]+',
    Case when ISNULL(cast(a.'+[name]+' as varchar(10)),'''') =ISNULL(cast(b.'+[name]+' as varchar(10)), '''') then ''Y'' else ''N'' End flag'+cast(rn as varchar(10))+','
    from cte1 
    
    set @sql= 'SELECT '+substring(@sql,1,len(@sql)-1)+' 
    FROM LegacyDB.dbo.' + @SRC_TBL + ' a  join  ApplicationDB.dbo. ' + @TGT_TBL + ' b on '+@join_colum
    Print (@sql)

    Let me know if you need anything else.

    Thanks!


    Known is a DROP, Unknown is an OCEAN.

    Monday, June 24, 2019 1:01 PM
  • Hi Bangaaram,

     

    Please try following script.

     
    /*Use LegacyDB
    go
    IF OBJECT_ID('E_App') IS NOT NULL drop table  E_App
    go 
    CREATE TABLE dbo.E_App(
    E_ID INT NOT NULL PRIMARY KEY,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY NUMERIC(19,2),
    A int
    );
    
    INSERT INTO E_App (E_ID, E_NM, E_ADR, E_SALARY,A)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00,1),
    (102, 'Dave', '332 Ave', 325.59,1),
    (103, 'Mark', '524 Cir', 9846.48,1),
    (104, 'Adam', '', 2313.10,1),
    (105, 'Ben', 'N 1st Street', NULL,1);
    
    USE ApplicationDB
    CREATE TABLE dbo.E_Legacy(
    E_ID INT NOT NULL PRIMARY KEY,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY DECIMAL(19,2)
    );
    
    
    INSERT INTO E_Legacy (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 825.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '8645 Street', 2313.10),
    (105, 'Jen', 'N 1st Street', 122.00);
    go
    */
    
    Use LegacyDB
    go
    declare @sql varchar(max)=''
    declare @i int =1
    declare @join_colum varchar(max)=''
    declare @SRC_TBL varchar(20)='E_App' 
    declare @TGT_TBL varchar(20)='E_Legacy' 
    
    ;with cte as (
    SELECT COLUMN_NAME FROM LegacyDB.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @SRC_TBL 
    ),cte1 as (
    SELECT COLUMN_NAME FROM ApplicationDB.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TGT_TBL
    )
    select @join_colum='a.'+a.COLUMN_NAME+'=b.'+b.COLUMN_NAME from cte a join cte1 b on a.COLUMN_NAME=b.COLUMN_NAME
    
    ;with cte as (
    SELECT distinct QUOTENAME(a.COLUMN_NAME) as [name],row_number()over(partition by (select 1) order by (select 1)) rn 
    from(select COLUMN_NAME from LegacyDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SRC_TBL)  a
    join (select COLUMN_NAME from ApplicationDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TGT_TBL)  b
    on a.COLUMN_NAME=b.COLUMN_NAME
    )
    select @sql=@sql+'
    a.'+[name]+', b.'+[name]+',
    Case when ISNULL(cast(a.'+[name]+' as varchar(10)),'''') =ISNULL(cast(b.'+[name]+' as varchar(10)), '''') then ''Y'' else ''N'' End flag'+cast(rn as varchar(10))+','
    from cte 
    
    set @sql= 'SELECT '+substring(@sql,1,len(@sql)-1)+' 
    FROM LegacyDB.dbo.' + @SRC_TBL + ' a  join  ApplicationDB.dbo. ' + @TGT_TBL + ' b on '+@join_colum
    Print (@sql)
    exec(@sql)
    
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 25, 2019 8:03 AM
  • Hi Rachel,

    Thanks! We just added another PK to our table to make it a Composite Key. The above code is only working when there is one PK on the table. How do I join on more than one PK at a time?


    Known is a DROP, Unknown is an OCEAN.

    Tuesday, June 25, 2019 1:53 PM
  • Hi Bangaaram,

     

    Please try following script.

     
    /*Use LegacyDB
    go
    IF OBJECT_ID('E_App') IS NOT NULL drop table  E_App
    go 
    CREATE TABLE dbo.E_App(
    E_ID INT NOT NULL ,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY NUMERIC(19,2)
    );
    
    ALTER TABLE E_App ADD CONSTRAINT [PK_E_App] PRIMARY KEY  
    (E_ID,E_NM)
    
    INSERT INTO E_App (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 325.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '', 2313.10),
    (105, 'Ben', 'N 1st Street', NULL);
    
    
    
    USE ApplicationDB
    IF OBJECT_ID('E_Legacy') IS NOT NULL drop table  E_Legacy
    go 
    CREATE TABLE dbo.E_Legacy(
    E_ID INT NOT NULL ,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY DECIMAL(19,2)
    );
    ALTER TABLE E_Legacy ADD CONSTRAINT [PK_E_Legacy] PRIMARY KEY  
    (E_ID,E_NM)
    
    
    INSERT INTO E_Legacy (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 825.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '8645 Street', 2313.10),
    (105, 'Jen', 'N 1st Street', 122.00);
    go
    */
    
    Use LegacyDB
    go
    declare @sql varchar(max)=''
    declare @i int =1
    declare @join_colum varchar(max)
    declare @SRC_TBL varchar(20)='E_App' 
    declare @TGT_TBL varchar(20)='E_Legacy' 
    
    ;with cte as (
    SELECT COLUMN_NAME FROM LegacyDB.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @SRC_TBL 
    ),cte1 as (
    SELECT COLUMN_NAME FROM ApplicationDB.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TGT_TBL
    )
    select @join_colum=ISNULL(@join_colum + ' and ','')+'a.'+a.COLUMN_NAME+'=b.'+b.COLUMN_NAME from cte a join cte1 b on a.COLUMN_NAME=b.COLUMN_NAME
    
    ;with cte as (
    SELECT distinct QUOTENAME(a.COLUMN_NAME) as [name],row_number()over(partition by (select 1) order by (select 1)) rn 
    from(select COLUMN_NAME from LegacyDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SRC_TBL)  a
    join (select COLUMN_NAME from ApplicationDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TGT_TBL)  b
    on a.COLUMN_NAME=b.COLUMN_NAME
    )
    select @sql=@sql+'
    a.'+[name]+', b.'+[name]+',
    Case when ISNULL(cast(a.'+[name]+' as varchar(10)),'''') =ISNULL(cast(b.'+[name]+' as varchar(10)), '''') then ''Y'' else ''N'' End flag'+cast(rn as varchar(10))+','
    from cte 
    
    set @sql= 'SELECT '+substring(@sql,1,len(@sql)-1)+' 
    FROM LegacyDB.dbo.' + @SRC_TBL + ' a  full outer join  ApplicationDB.dbo. ' + @TGT_TBL + ' b on '+@join_colum
    Print (@sql)
    exec(@sql)
    /*
    SELECT 
    a.[E_ADR], b.[E_ADR],
    Case when ISNULL(cast(a.[E_ADR] as varchar(10)),'') =ISNULL(cast(b.[E_ADR] as varchar(10)), '') then 'Y' else 'N' End flag1,
    a.[E_ID], b.[E_ID],
    Case when ISNULL(cast(a.[E_ID] as varchar(10)),'') =ISNULL(cast(b.[E_ID] as varchar(10)), '') then 'Y' else 'N' End flag2,
    a.[E_NM], b.[E_NM],
    Case when ISNULL(cast(a.[E_NM] as varchar(10)),'') =ISNULL(cast(b.[E_NM] as varchar(10)), '') then 'Y' else 'N' End flag3,
    a.[E_SALARY], b.[E_SALARY],
    Case when ISNULL(cast(a.[E_SALARY] as varchar(10)),'') =ISNULL(cast(b.[E_SALARY] as varchar(10)), '') then 'Y' else 'N' End flag4 
    FROM LegacyDB.dbo.E_App a  full outer join  ApplicationDB.dbo. E_Legacy b on a.E_ID=b.E_ID and a.E_NM=b.E_NM
    E_ADR                                              E_ADR                                              flag1 E_ID        E_ID        flag2 E_NM                           E_NM                           flag3 E_SALARY                                E_SALARY                                flag4
    -------------------------------------------------- -------------------------------------------------- ----- ----------- ----------- ----- ------------------------------ ------------------------------ ----- --------------------------------------- --------------------------------------- -----
    123 Blvd                                           123 Blvd                                           Y     101         101         Y     John                           John                           Y     2500.00                                 2500.00                                 Y
    332 Ave                                            332 Ave                                            Y     102         102         Y     Dave                           Dave                           Y     325.59                                  825.59                                  N
    524 Cir                                            524 Cir                                            Y     103         103         Y     Mark                           Mark                           Y     9846.48                                 9846.48                                 Y
                                                       8645 Street                                        N     104         104         Y     Adam                           Adam                           Y     2313.10                                 2313.10                                 Y
    N 1st Street                                       NULL                                               N     105         NULL        N     Ben                            NULL                           N     NULL                                    NULL                                    Y
    NULL                                               N 1st Street                                       N     NULL        105         N     NULL                           Jen                            N     NULL                                    122.00                                  N
    */

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 26, 2019 9:37 AM
  • Hi Rachel,

    Thanks for the updated query and it runs fine with these two tables. To make sure it works uniformly, I created the E_Legacy table in LegacyDB and E_App table in ApplicationDB and tried running the query. I ended up with the same error.

    Msg 537, Level 16, State 3, Line 43
    Invalid length parameter passed to the LEFT or SUBSTRING function.


    Known is a DROP, Unknown is an OCEAN.

    Wednesday, June 26, 2019 5:56 PM
  • Hi Bangaaram,

     

    Thank you for your kindly reply.

     

    In my above, I create table 'E_App' in LegacyDB and create table 'E_Legacy' in ApplicationDB. I am sorry that I might have an error understanding. If you would like create table 'E_App' in ApplicationDB and create table 'E_Legacy' in LegacyDB , please change the value of @SRC_TBL and @TGT_TBL.

     

    Please try following script.

    /*Use ApplicationDB
    go
    IF OBJECT_ID('E_App') IS NOT NULL drop table  E_App
    go 
    CREATE TABLE dbo.E_App(
    E_ID INT NOT NULL ,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY NUMERIC(19,2)
    );
    
    ALTER TABLE E_App ADD CONSTRAINT [PK_E_App] PRIMARY KEY  
    (E_ID,E_NM)
    
    INSERT INTO E_App (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 325.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '', 2313.10),
    (105, 'Ben', 'N 1st Street', NULL);
    
    
    
    USE LegacyDB
    IF OBJECT_ID('E_Legacy') IS NOT NULL drop table  E_Legacy
    go 
    CREATE TABLE dbo.E_Legacy(
    E_ID INT NOT NULL ,
    E_NM VARCHAR(30) NOT NULL,
    E_ADR VARCHAR(50),
    E_SALARY DECIMAL(19,2)
    );
    ALTER TABLE E_Legacy ADD CONSTRAINT [PK_E_Legacy] PRIMARY KEY  
    (E_ID,E_NM)
    
    
    INSERT INTO E_Legacy (E_ID, E_NM, E_ADR, E_SALARY)
    VALUES 
    (101, 'John', '123 Blvd', 2500.00),
    (102, 'Dave', '332 Ave', 825.59),
    (103, 'Mark', '524 Cir', 9846.48),
    (104, 'Adam', '8645 Street', 2313.10),
    (105, 'Jen', 'N 1st Street', 122.00);
    go
    */
    
    Use LegacyDB
    go
    declare @sql varchar(max)=''
    declare @i int =1
    declare @join_colum varchar(max)
    declare @SRC_TBL varchar(20)='E_Legacy' --------LegacyDB
    declare @TGT_TBL varchar(20)='E_App' --------ApplicationDB
    
    ;with cte as (
    SELECT COLUMN_NAME FROM LegacyDB.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME =@SRC_TBL
    ),cte1 as (
    SELECT COLUMN_NAME FROM ApplicationDB.INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TGT_TBL
    )
    select @join_colum=ISNULL(@join_colum + ' and ','')+'a.'+a.COLUMN_NAME+'=b.'+b.COLUMN_NAME from cte a join cte1 b on a.COLUMN_NAME=b.COLUMN_NAME
    
    ;with cte as (
    SELECT distinct QUOTENAME(a.COLUMN_NAME) as [name],row_number()over(partition by (select 1) order by (select 1)) rn 
    from(select COLUMN_NAME from LegacyDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @SRC_TBL)  a
    join (select COLUMN_NAME from ApplicationDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TGT_TBL)  b
    on a.COLUMN_NAME=b.COLUMN_NAME
    )
    select @sql=@sql+'
    a.'+[name]+', b.'+[name]+',
    Case when ISNULL(cast(a.'+[name]+' as varchar(10)),'''') =ISNULL(cast(b.'+[name]+' as varchar(10)), '''') then ''Y'' else ''N'' End flag'+cast(rn as varchar(10))+','
    from cte 
    
    set @sql= 'SELECT '+substring(@sql,1,len(@sql)-1)+' 
    FROM LegacyDB.dbo.' + @SRC_TBL + ' a  full outer join  ApplicationDB.dbo. ' + @TGT_TBL + ' b on '+@join_colum
    Print (@sql)
    exec(@sql)
    /*
    SELECT 
    a.[E_ADR], b.[E_ADR],
    Case when ISNULL(cast(a.[E_ADR] as varchar(10)),'') =ISNULL(cast(b.[E_ADR] as varchar(10)), '') then 'Y' else 'N' End flag1,
    a.[E_ID], b.[E_ID],
    Case when ISNULL(cast(a.[E_ID] as varchar(10)),'') =ISNULL(cast(b.[E_ID] as varchar(10)), '') then 'Y' else 'N' End flag2,
    a.[E_NM], b.[E_NM],
    Case when ISNULL(cast(a.[E_NM] as varchar(10)),'') =ISNULL(cast(b.[E_NM] as varchar(10)), '') then 'Y' else 'N' End flag3,
    a.[E_SALARY], b.[E_SALARY],
    Case when ISNULL(cast(a.[E_SALARY] as varchar(10)),'') =ISNULL(cast(b.[E_SALARY] as varchar(10)), '') then 'Y' else 'N' End flag4 
    FROM LegacyDB.dbo.E_Legacy a  full outer join  ApplicationDB.dbo. E_App b on a.E_ID=b.E_ID and a.E_NM=b.E_NM
    E_ADR                                              E_ADR                                              flag1 E_ID        E_ID        flag2 E_NM                           E_NM                           flag3 E_SALARY                                E_SALARY                                flag4
    -------------------------------------------------- -------------------------------------------------- ----- ----------- ----------- ----- ------------------------------ ------------------------------ ----- --------------------------------------- --------------------------------------- -----
    123 Blvd                                           123 Blvd                                           Y     101         101         Y     John                           John                           Y     2500.00                                 2500.00                                 Y
    332 Ave                                            332 Ave                                            Y     102         102         Y     Dave                           Dave                           Y     825.59                                  325.59                                  N
    524 Cir                                            524 Cir                                            Y     103         103         Y     Mark                           Mark                           Y     9846.48                                 9846.48                                 Y
    8645 Street                                                                                           N     104         104         Y     Adam                           Adam                           Y     2313.10                                 2313.10                                 Y
    NULL                                               N 1st Street                                       N     NULL        105         N     NULL                           Ben                            N     NULL                                    NULL                                    Y
    N 1st Street                                       NULL                                               N     105         NULL        N     Jen                            NULL                           N     122.00                                  NULL                                    N
    */


    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 27, 2019 9:03 AM
  • Still getting the same error, Rachel.

    Known is a DROP, Unknown is an OCEAN.

    Thursday, June 27, 2019 1:38 PM
  • Hi,

    Thank you for your kind reply.

    Could you  please share us your table structure and some sample data along with your script ? So that we’ll get a right direction and make some test.

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 28, 2019 1:23 AM