Combining Tables to See Differences

1

Hello,

I am trying to show the differences between two different tables, on one table. How would I go about this?

I have both of my queries, but when I use UNION ALL this does not put the differences side by side.

I need them side by side, any help is appreciated!

Thank you!

2

What I usually do when I need to compare side-by-side is export the data to a file and use a file comparison utility.

This can be done easily in a single query window using SQLCMD mode:

:connect servera
:out c:\temp\servera.txt

Select ... From ... Where ...;
Go

:connect serverb
:out c:\temp\serverb.txt

Select ... From ... Where ...;
Go

Now just use your favorite file comparison tool to compare the files to see the differences. My favorite tool is 'Beyond Compare' by Scooter Software.

Note: make sure both queries are sorting the data the same way and that your file comparison tool is able to read that type of output.

3

Another option is to open 2 query windows and place them side-by-side...

Now - if you need to do this all in a single query:

with query1
as (
select ... from ... where ...;
)
, query2
as (
select ... from ... where ...;
)
Select *
From query1
full outer join query2 on query1.key = query2.key

It all depends on what you are trying to actually accomplish.

4

hi

there is EXCEPT clause

select 'there in top table ..not there in bottom table',* from #temp
except
select 'there in top table ..not there in bottom table',* from #temp1
go
image

image

5

hi

another way is to do a join

select a.*
from table a join table b
on a.col1 <> b.col1 and a.col2 <> b.col2 and a.col3 <> b.col3

6

A full outer join is a way to compare values in tables joined by a column. If you want to see what's in one of the tables and not in the other you can use a union query to eliminate the rows that match.

CREATE TABLE #FIRST(ID INT PRIMARY KEY, VAL VARCHAR(20));
INSERT #FIRST (ID, VAL)
VALUES
  (1, 'ONE')
, (2, 'TWO')
, (3, 'THREE')
, (7, 'SEVEN')
, (16, 'SIXTEEN');
CREATE TABLE #SECOND(ID INT PRIMARY KEY, VAL VARCHAR(20));
INSERT #SECOND (ID, VAL)
VALUES
  (6, 'SIX')
, (7, 'SEVEN')
, (9, 'NINE')
, (10, 'TEN')
, (16, 'SIXTEEN');

SELECT F.ID, F.VAL, S.ID, S.VAL
FROM #FIRST F
FULL OUTER JOIN #SECOND S
ON F.ID = S.ID;

SELECT F.ID, F.VAL, S.ID, S.VAL
FROM #FIRST F
LEFT JOIN #SECOND S
ON F.ID = S.ID
WHERE S.ID IS NULL
UNION
SELECT F.ID, F.VAL, S.ID, S.VAL
FROM #FIRST F
RIGHT JOIN #SECOND S
ON F.ID = S.ID
WHERE F.ID IS NULL;
                                               
DROP TABLE #FIRST;

DROP TABLE #SECOND;

image