locked
compare two result sets RRS feed

  • Question

  • hi,

    i would like to compare the result of two different queries.

    for example :

    query1 : select Col1,Col2 from TableX

    query2 : select ColA,ColB from TableY

    now i would like to compare these two (query1, query2)  result sets.

    IF (query1 = query2)

    print '1';

    Else Print '2';

    can we do this ?

    If yes , please specify it.

    Thanks.


    Friday, August 12, 2011 9:36 AM

Answers

  • Something on the lines of this may work. Excuse the geek in me.. :) 

    CREATE TABLE #TABLE1 (
    ID INT,
    NAME NVARCHAR(30)
    )
    
    CREATE TABLE #TABLE2 (
    ID INT,
    NAME NVARCHAR(30)
    )
    
    INSERT INTO #TABLE1 (ID,NAME) VALUES 
    (1,'SAY WHAT!!'),
    (2,'NO WAY!!!'),
    (3,'SAY WHO!!')
    
    
    INSERT INTO #TABLE2 (ID,NAME) VALUES 
    (1,'SAY WHAT!!'),
    (2,'NO WAY!!!'),
    (3,'SAY WHO!!')
    
    SELECT ID,NAME FROM #TABLE1 
    EXCEPT
    SELECT ID,NAME FROM #TABLE2 
    
    
    SELECT
    CASE WHEN @@ROWCOUNT = 0
    	THEN '1 - THEY ARE SAME!!'
    	ELSE '0 - NOPE THEY ARE DIFFERENT !!'
    END 	
    
    DROP TABLE #TABLE1
    DROP TABLE #TABLE2
    



    Please mark posts as answer or helpful when they are.
    • Proposed as answer by Kent Waldrop Friday, August 12, 2011 11:00 AM
    • Marked as answer by KJian_ Friday, August 19, 2011 7:56 AM
    Friday, August 12, 2011 10:33 AM

All replies

  • Yes we can -If you have common column between two tabels then its possible to write query to compare two tables.

    Other option would be red-gate SQL datacompare tool.


    http://uk.linkedin.com/in/ramjaddu
    Friday, August 12, 2011 9:39 AM
  • Something on the lines of this may work. Excuse the geek in me.. :) 

    CREATE TABLE #TABLE1 (
    ID INT,
    NAME NVARCHAR(30)
    )
    
    CREATE TABLE #TABLE2 (
    ID INT,
    NAME NVARCHAR(30)
    )
    
    INSERT INTO #TABLE1 (ID,NAME) VALUES 
    (1,'SAY WHAT!!'),
    (2,'NO WAY!!!'),
    (3,'SAY WHO!!')
    
    
    INSERT INTO #TABLE2 (ID,NAME) VALUES 
    (1,'SAY WHAT!!'),
    (2,'NO WAY!!!'),
    (3,'SAY WHO!!')
    
    SELECT ID,NAME FROM #TABLE1 
    EXCEPT
    SELECT ID,NAME FROM #TABLE2 
    
    
    SELECT
    CASE WHEN @@ROWCOUNT = 0
    	THEN '1 - THEY ARE SAME!!'
    	ELSE '0 - NOPE THEY ARE DIFFERENT !!'
    END 	
    
    DROP TABLE #TABLE1
    DROP TABLE #TABLE2
    



    Please mark posts as answer or helpful when they are.
    • Proposed as answer by Kent Waldrop Friday, August 12, 2011 11:00 AM
    • Marked as answer by KJian_ Friday, August 19, 2011 7:56 AM
    Friday, August 12, 2011 10:33 AM
  • Hi Sudeep,

    if you are using sql server 2005 & above, you can use EXCEPT and INTERSECT to accomplish same

    please check below link:

    http://msdn.microsoft.com/en-us/library/ms188055.aspx

    Hope this helps!!


    Regards, Vishal Srivastava
    Friday, August 12, 2011 10:37 AM
  • you can use EXCEPT and INTERSECT statements.

    EXCEPT returns any distinct values from the left query that are not also found on the right query.

    INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

    Syntax:

    {<query_specification> | ( <query_expression> ) }
    { EXCEPT | INTERSECT }
    { <query_specification> | ( <query_expression> ) }


    Thanks & Regards, Leela Prasad
    Friday, August 12, 2011 10:39 AM
  • I don't think this is correct. It shows that Table1 is either the same as Table2 (i.e. improper subset) or a (proper) subset. If Table2 has an extra row (4, 'no way!'), the result is the same.
    Monday, March 25, 2019 4:09 PM
  • I don't think this is correct. It shows that Table1 is either the same as Table2 (i.e. improper subset) or a (proper) subset. If Table2 has an extra row (4, 'no way!'), the result is the same.
    Monday, March 25, 2019 4:09 PM