Compare two HUGE Table's HELL!!!

[Dineedshelp]

Hi PLEASE HELP!!!,

I have two tables that are identical in everysense, apart from thier names.

I have a dts that runs overnight to move the info from table a to table b. Tables a currently have 55578 records, table b 50986 records. I am losing 4592 records in the transfer.

Is there any easy way of finding out what records are missing from table b, to see if I can identify the problem?

Both tables have 60-70 columns in them, so I don't want to do anything (if possible) that means I have to do it column by column.

Thanks!!!!

 

[mrdenny]

Something like this will do the trick.

select *
from tableb
where not exists (select {key columns} from tablea)

That should bring up the missing rows.

Just so you know 55k records isn't all that many. When you hit half a billion, now that's a big table.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[vongrunt]

There is one distant possibility with CHECKSUM() function:

select *
from tableA
where checksum(*) not in (select checksum(*) from tableB)

... but it isn't always accurate (two sets of values may have the same checksum) and won't work if you have automated columns (identity w/o forced insert, timestamp) or text/ntext/image columns.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[donutman]

I think mrdenny meant this for the Where clause

[Blue]WHERE[/Blue] {key column} [Gray]NOT[/Gray] [Blue]IN[/Blue] [Gray]([/Gray][Blue]SELECT[/Blue] {key column} [Blue]FROM[/Blue] tablea[Gray])[/Gray]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]