asked
datasets compare
Hi I have two datasets ![alt text][1] [1]: /storage/temp/1198-untitled.png now i need to compare the entire dataset not row by row for example i need to compare Table A & Table B since there is a difference in the managerid for Jack i need to update the 3 rows in TableB and not just the Row for Jack Kindly provide your suggestions. I tried Merge but again that was row by row. Thanks, Kannan
What happens if there are extra rows in either table? Does that constitute a difference?
no other rows in table B should not be affected, Actually I have managed to get these datasets in two CTEs so i could compare the datasets only
is that a double negative, or should there be a comma? no other rows in table B should not be affected = other rows in table B should be affected or no, other rows in table B should not be affected Sorry to be picky, but it makes a difference...
To compare whole data sets, you could use a combination of EXCEPT and UNION. In this solution, if any rows are different then the code to make the second table look like the first is executed. declare @TableA table (empid int, empname varchar(10), managerid int, parentid int) insert into @TableA select 1, 'john', null, 1 insert into @TableA select 1, 'jenny', 1, 0 insert into @TableA select 1, 'jack', 1, 0 declare @TableB table (empid int, empname varchar(10), managerid int, parentid int) insert into @TableB select 1, 'john', null, 1 insert into @TableB select 1, 'jenny', 1, 0 insert into @TableB select 1, 'jack', 2, 0 if exists ( select empid, empname, managerid, parentid from @TableA except select empid, empname, managerid, parentid from @TableB union all select empid, empname, managerid, parentid from @TableB except select empid, empname, managerid, parentid from @TableA ) begin --there are some differences --make @TableB look like @TableA delete from @TableB --you might use TRUNCATE if your table is a 'real' table insert into @TableB (empid,empname,managerid,parentid) select empid,empname,managerid,parentid from @TableA end select * from @TableA select * from @TableB
That would be true if both tables contain the 'same' rows, but at the time, the OP hadn't confirmed what to do about extra rows.
Nice solution @KevRiley. I had a question- Why was the "union all" used? Since Table B is made to look like Table A, wouldn't just this suffice? if exists ( select empid, empname, managerid, parentid from @TableB except select empid, empname, managerid, parentid from @TableA) begin --there are some differences
14 People are following this question.
