Bringing in a New Table to Refresh an Existing One; Record Counts are Different; How do I say "Gimme the discrepant records?"

Brian C Hart

So here I have a table, TABLE1, which I copied over from a different server: SELECT COUNT(*) FROM TABLE1 gives me, e.g., 83977 records. However, there's another table, TABLE2, which lives on the current server, and I want to know if TABLE1 and TABLE2 have identically the same data. That's kind of a different question than I am asking here, because that question depends on how the tables are indexed, keyed etc. For now, however, I do a SELECT COUNT(*) FROM TABLE2 and it gives 83911 records. So the two tables differ by 66 records. How do I say to the database, "OK, what are those 66 records?" I tried a LEFT OUTER JOIN between TABLE1 (on the left) and TABLE2 (on the right) and I join on the tables' keys and filter on the right table's keys being NULL. I get 130 rows. I'm confused, shouldn't I have 66 rows? Brian

Sincerely Yours, Brian Hart

David Skelly

Assume table A contains keys: a b c d and table B contains keys: a b e f Both tables contain 4 rows. However, an oouter join of the kind you describe would return 2 rows (c and d). In your case, table 1 contains 130 keys which are not in table 2. Table 2 contains 64 rows which are not in table 1. Hence, the difference in size is 66 rows (130 - 64).

Chris Meech

What you are really after is a union of what is in TABLE1, that's not in TABLE2, along with what is in TABLE2, that's not in TABLE1. I'd try the following

select * from TABLE1 t1
where not exists ( select * from TABLE2 t2 where t1.key = t2.key )
union all
select * from TABLE2 t2
where not exists ( select * from TABLE1 t1 where t2.key = t1.key )

You might want to create a view for this, depending upon the circumstances. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Brian C Hart

Nice try, but TABLE1 and TABLE2 have completely different record counts (right, they are off by 66) so the database complains: Msg 205, Level 16, State 1, Line 1 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Sincerely Yours, Brian Hart

Chris Meech

If that is the error message, that means the the * which is being selected in each case is different. Replace the * in my select with just the key identifier columns and it should work. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Mycroft Holmes

[Product plug] We use Red-Gate SQL data compare for this job and it is excellent but a bit expensive for a single requirement. [/Product plug] Do an inner join between the 2 tables using EVERY column, this will identify the identical records, then select from the(s) where the Ids are not in the inner join. Inspect this result and discard the joins that are not relevant and repeat. When you have the critical records decide what you need to do to merge them.

Never underestimate the power of human stupidity RAH

Yusuf

I know you are trying to run a query to find the different rows. Would using external tool acceptable? If so, look at SQLDBDiff [^]which is freeware and does decent job comparing DB and more.

Yusuf May I help you?

i j russell

Use FULL OUTER JOIN.

CREATE TABLE #first
(
Id INT NOT NULL,
Name VARCHAR(10) NOT NULL
);

CREATE TABLE #second
(
Id INT NOT NULL,
Name VARCHAR(10) NOT NULL
);

INSERT INTO #first (Id, Name) VALUES (1, 'One');
INSERT INTO #first (Id, Name) VALUES (2, 'Two');
INSERT INTO #first (Id, Name) VALUES (3, 'Three');
INSERT INTO #first (Id, Name) VALUES (9, 'Nine');

INSERT INTO #second (Id, Name) VALUES (2, 'Two');
INSERT INTO #second (Id, Name) VALUES (3, 'Three');
INSERT INTO #second (Id, Name) VALUES (5, 'Five');
INSERT INTO #second (Id, Name) VALUES (7, 'Seven');

SELECT f.Id AS fid,
f.Name AS fname,
s.Id AS [sid],
s.Name AS sname
FROM #first f
FULL OUTER JOIN #second s
ON s.Id = f.Id
WHERE s.Id IS NULL OR f.Id IS NULL

DROP TABLE #first;
DROP TABLE #second;

David Skelly

It's not complaining because the number of rows don't match. It's complaining because the number of columns don't match. See Chris's previous answer for how a suggestion on how to correct the query.