How to get RowCount and ResultSet from CTE

1

Hi,

I have this query

CREATE TABLE #Summary(SourceQueryRowCount INT, TargetQueryRowCount INT, QueryDiffRowCount INT)
CREATE TABLE #CompareResult(DataSource varchar(50), ID int, Rule1 varchar(50), Rule2 varchar(50))

;WITH SourceQuery AS
(
SELECT * FROM (VALUES( 1, 'a','b'),(2, 'c','d'),(3,'e','f'))rs(id,rule1,rule2)
WHERE rs.rule1 != 'a'
),
TargetQuery AS
(
SELECT * FROM (VALUES( 1, 'a','b'),(2, 'c','d'),(3,'e','f'))rs(id,rule1,rule2)
)
INSERT INTO #CompareResult
SELECT DataSource = 'SourceToTarget', * FROM SourceQuery EXCEPT SELECT DataSource = 'SourceToTarget', * FROM TargetQuery
UNION ALL
SELECT DataSource = 'TargetToSource', * FROM TargetQuery EXCEPT SELECT DataSource = 'TargetToSource', * FROM SourceQuery

and I want to get rows count in variable or #Summary table and compare result set into table #CompareResult.

I did this with create seprate temp table for SourceQuery and TargetQuery and then compare it but as source and target queries result set are huge and i want to perform every thing in one set. is this possible.

Thanks

2 
hi 

there are operators like 
EXCEPT INTERSECT which can do that ..


select result set1 
EXCEPT 
select result set12

will give records in result 1 that are not here in result set 2

select result set1 
INTERSECT 
select result set12

will give records  that are there "common" in result set 2 and result 1

if you want do some other type of compare .. depends on your data set 
Strategy has to be thought of and SQL specific for that has to be written 



please see these