locked
TSQL Except statement to compare output of several different views which have different collation sequence RRS feed

  • Question

  • I'd like to compare the output of two different views to ensure data has been migrated successfully. 

    The following would work great: 

    select * from (select * from query1) as query1
    except
    select * from (select * from query2) as query2

    The issue is that "query2" is based in a different database with a different collation sequence than query1. 

    How can I utilize this concept to show the output differences between query1 and query2 with the different collation sequences ? 

    I'm trying to avoid adding a lot of Collate statements after each varchar/char datatype. 

    Thank you

    Monday, June 8, 2020 11:02 PM

Answers

  • Hi vsdla,

    I don't know what is the reason why you posted your question on the 'SQL Server XML' forum.

    In any case, here is XML, XQuery, and FLWOR based solution for your scenario with two tables, source and destination.

    Conversion to XML makes sure that all the data from both tables is in the UTF-16 encoding.

    SQL:

    -- DDL and sample data population, start
    DECLARE @source TABLE (id INT PRIMARY KEY, city VARCHAR(30) NULL);
    DECLARE @target TABLE (id INT PRIMARY KEY, city VARCHAR(30) NULL);
    
    INSERT INTO @source (id, city) VALUES
    (1, 'Miami')
    ,(2, 'Hollywood');
    
    INSERT INTO @target (id, city) VALUES
    (1, 'Miami')
    ,(2, 'Fort Lauderdale');
    -- DDL and sample data population, end
    
    SELECT (
    	SELECT NULL, (
    		SELECT * FROM @source ORDER BY id
    		FOR XML PATH('r'), TYPE, ROOT('source')
    	), (
    		SELECT * FROM @target ORDER BY id
    		FOR XML PATH('r'), TYPE, ROOT('target')
    )
    FOR XML PATH(''), TYPE, ROOT('root')
    ).query('<root>
    {
    	for $x in /root/source/r
    	let $pos := count(root/source/r[. << $x]) + 1
    	return <r row="{$pos}">
    	{
    		if (every $r in $x/*/text()
                satisfies $r = (/root/target/r[$pos]/*/text()))
            then  
                 <Result>Match</Result>  
             else  
                 <Result>No match</Result>
    	}
    	</r>
    }
    </root>');


    Output:

    <root>
      <r row="1">
        <Result>Match</Result>
      </r>
      <r row="2">
        <Result>No match</Result>
      </r>
    </root>

    • Edited by Yitzhak Khabinsky Tuesday, June 9, 2020 1:14 AM
    • Marked as answer by vsdla Tuesday, June 9, 2020 9:56 PM
    Tuesday, June 9, 2020 1:06 AM

All replies

  • Hi vsdla,

    I don't know what is the reason why you posted your question on the 'SQL Server XML' forum.

    In any case, here is XML, XQuery, and FLWOR based solution for your scenario with two tables, source and destination.

    Conversion to XML makes sure that all the data from both tables is in the UTF-16 encoding.

    SQL:

    -- DDL and sample data population, start
    DECLARE @source TABLE (id INT PRIMARY KEY, city VARCHAR(30) NULL);
    DECLARE @target TABLE (id INT PRIMARY KEY, city VARCHAR(30) NULL);
    
    INSERT INTO @source (id, city) VALUES
    (1, 'Miami')
    ,(2, 'Hollywood');
    
    INSERT INTO @target (id, city) VALUES
    (1, 'Miami')
    ,(2, 'Fort Lauderdale');
    -- DDL and sample data population, end
    
    SELECT (
    	SELECT NULL, (
    		SELECT * FROM @source ORDER BY id
    		FOR XML PATH('r'), TYPE, ROOT('source')
    	), (
    		SELECT * FROM @target ORDER BY id
    		FOR XML PATH('r'), TYPE, ROOT('target')
    )
    FOR XML PATH(''), TYPE, ROOT('root')
    ).query('<root>
    {
    	for $x in /root/source/r
    	let $pos := count(root/source/r[. << $x]) + 1
    	return <r row="{$pos}">
    	{
    		if (every $r in $x/*/text()
                satisfies $r = (/root/target/r[$pos]/*/text()))
            then  
                 <Result>Match</Result>  
             else  
                 <Result>No match</Result>
    	}
    	</r>
    }
    </root>');


    Output:

    <root>
      <r row="1">
        <Result>Match</Result>
      </r>
      <r row="2">
        <Result>No match</Result>
      </r>
    </root>

    • Edited by Yitzhak Khabinsky Tuesday, June 9, 2020 1:14 AM
    • Marked as answer by vsdla Tuesday, June 9, 2020 9:56 PM
    Tuesday, June 9, 2020 1:06 AM
  • Thank you, Yitzhak. It was intended for the TSQL forum. That was an error. Appreciate your input!.
    Tuesday, June 9, 2020 9:56 PM