I'm rewriting a process and need to compare the new process results with the current process. The process outputs a table with tens of millions of rows with 100 fields. Most of the fields are text, with a few timestamps and some numbers. The primary key is a concatenation of 5 fields.
I need to identify:
1 - rows that are in the original table and not in the new process table
2 - rows that are in the new process table and not the original table
3 - rows with the same key but different contents in the non-key fields, and what the differences are.
Has anyone done something similar and be willing to share an efficient methodolgy and code?
Well, hopefully, since the flags were defined using a boolean expression, they'll take very little memory. But looking at the .mem file, it seems they take 4 bytes each. Well, what if we use -1/null instead of -1/0? Now we're talking. Assuming differences are sparse, you won't have nearly as many values established, and it looks like they're one byte each if I'm reading the .mem file correctly. Example updated.
did you find a solution for such a comparison? I need to compare two tables comparable to yours, but with just about 60 fields. My tryings resulted in an memory error.
I've tested it the first time with concatenating all fields fields together and using the exists() function to get the different rows. Second I've tried John's solution with the flags, but get the error already with four fields.
