Minus

We can compare two queries by db engine (on one server) using MINUS/EXCEPT Sql operator

Following query is executed in db engine:

(Q1 EXCEPT Q2) Union (Q2 EXCEPT Q1)

do not put semicolon at the end of query.

<cmpSqlResultsTest>
    <compare mode="MINUS" defaultDatasourceName="SQL_SERVER" diffTableSize="5" minusQueryIndicatorOn="true" fileOutputOn="false">
        <sql minusQueryIndicatorOccurence="1" minusQueryIndicatorText="ImportLayer">
         <![CDATA[
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
  FROM [AdventureWorks2008R2].[Person].[Address]
  where AddressID <> 1
        ]]>
        </sql>
        <sql minusQueryIndicatorOccurence="1" minusQueryIndicatorText="ModelLayer" >
        <![CDATA[
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
  FROM [AdventureWorks2008R2].[Person].[Address]
    where AddressID <> 2
        ]]>
        </sql>
    </compare>
</cmpSqlResultsTest>

• defaultDatasourceName - name of connection to database engine defined in cmpSqlResults-config.xml file
• diffTableSize - number of differences which will be displayed in logs. Even records from first DB/SQL and odd from the second (so the table will have two times more rows than defined here). Default is 5.

When diffTableSize="0" is set - count query is executed:

Select count(*) from ((Q1 EXCEPT Q2) Union (Q2 EXCEPT Q1)) countTable

and minusQueryIndicatorOn="true" is ignored

• fileOutputOn - boolean value - if true all results will be saved in CSV files (same name as XML test configuration file but with different prefix and extension). File may be incomplete (if chunk > 0 - see above).In case of MINUS comparator only 1 file will be generated: test_name_minus.csv - CSV with results returned by MINUS sql query Default is false.
• minusQueryIndicatorOn - by default is set to 'false', Minus Query Indicator for easiest analysis results of Minus\Except query, if set to 'true' text columns are added, given occurrence set in minusQueryIndicatorOccurence (find by regex expression "(?i:F)(?i:R)(?i:O)(?i:M)") of 'From' clause in queries is replaced by given text in minusQueryIndicatorText
• minusQueryIndicatorOccurence -by default is set to 1, number of occurrence of 'From' clause to be replaced when minusQueryIndicatorOn is set to 'true'
• minusQueryIndicatorText - by default it is set accordingly to 'query1' or 'query2' when minusQueryIndicatorOn is set to 'true', 'text' column added to queries for easiest analysis of found diffrences.

(         
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
  ,'ImportLayer' 
 From  [AdventureWorks2008R2].[Person].[Address]
  where AddressID <> 1
        
         EXCEPT 
        
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
  ,'ImportLayer' 
 From  [AdventureWorks2008R2].[Person].[Address]
    where AddressID <> 2
        
        ) UNION (
        
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
  ,'ModelLayer' 
 From  [AdventureWorks2008R2].[Person].[Address]
    where AddressID <> 2
        
         EXCEPT 
         
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
  ,'ModelLayer' 
 From  [AdventureWorks2008R2].[Person].[Address]
  where AddressID <> 1
        )

Additional columns helps to indicate from which query differences were returned

[minus_many_xml_files.PersonAddress_SQL_SERVER_first] Differences in SQL queries found -  expected [0] but found [2]
[minus_many_xml_files.PersonAddress_SQL_SERVER_first] The table of differences (2 rows):
 ______________________________________________________________________________________________________________________ 
|AddressID|AddressLine1        |AddressLine2|City     |StateProvinceID|PostalCode|ModifiedDate           |             |
|_________|____________________|____________|_________|_______________|__________|_______________________|_____________|
|'2'      |'9833 Mt. Dias Blv.'|'<NULL>'    |'Bothell'|'79'           |'98011'   |'2003-01-01 00:00:00.0'|'ImportLayer'|
|'1'      |'1970 Napa Ct.'     |'<NULL>'    |'Bothell'|'79'           |'98011'   |'2002-01-04 00:00:00.0'|'ModelLayer' |
|_________|____________________|____________|_________|_______________|__________|_______________________|_____________|