I am trying to compare certain objects serialized to a database. For any such comparison, I know the IDs of two such objects. Let's call them
X with columns
C contains tuples that are linked to
o2, respectively. Thus, each row in
X is identified by its parent (
o2) and an
ID column unique among all rows that belong to the same parent.
The comparison of
o2 is done by finding items in
X that exist only for
o1 or only for
o2 (equality of items assumed based upon their local
ID), and by finding pairs of items for which at least one of them columns
C has different values. For each difference, a row needs to be added to a
My goal is to use a stored procedure to conduct this comparison.
For each item in
Diff table can receive any number of rows. Therefore, I think an
INSERT ... SELECT ... statement is not the right way to go. (At least I do not see how to proceed using one.)
Instead, it seems I have to implement this comparison somehow "procedurally", by "manually" comparing column by column, after an outer join. Thus, I have started like this:
CREATE PROCEDURE Compare (@o1 INT, @o2 INT) AS BEGIN DECLARE @o1x INT; @o2x INT; DECLARE @o1A INT, @o2A INT; DECLARE @o1B INT, @o2B INT; DECLARE @o1C INT, @o2C INT; DECLARE XCursor CURSOR FOR SELECT x1.ID, x1.A, x1.B, x1.C, x2.ID, x2.A, x2.B, x2.C FROM X x1 FULL OUTER JOIN X x2 ON x1.ID = x2.ID WHERE x1.PARENT = o1 AND x2.PARENT = o2; OPEN XCursor WHILE 1=1 BEGIN FETCH NEXT FROM XCursor INTO @o1x, @o1A, @o1B, @o1C, @o2x, @o2A, @o2B, @o2C IF @@fetch_status <> 0 BREAK IF @o1x IS NULL INSERT INTO Diff (Object, DiffDescription) VALUES (@o2x, 'O1 X is missing'); IF @o2x IS NULL INSERT INTO Diff (Object, DiffDescription) VALUES (@o1x, 'O2 X is missing'); IF @o1A <> @o2A INSERT INTO Diff (Object, DiffDescription) VALUES (@o1x, 'A is different.'); IF @o1B <> @o2B INSERT INTO Diff (Object, DiffDescription) VALUES (@o1x, 'B is different.'); IF @o1C <> @o2C INSERT INTO Diff (Object, DiffDescription) VALUES (@o1x, 'C is different.'); END CLOSE XCursor DEALLOCATE XCursor END
(This is not guaranteed to be 100% syntactically correct already. I am trying to illustrate the general idea.)
My question is: This seems really cumbersome and hard to maintain1. Is there a more concise way of conducting this comparison, especially a typical pattern normally employed for such use cases?
Obviously I have heavily anonymized and generalized my example DB schema and code. In the actual code, there are not just three columns
C, but more like 20; there is not just one table
X, but further sub-elements linked from
X, and the
Diff table is quite a bit more elaborate and requires more detailed data. The actual code that I have for now is accordingly more complex.
1: For instance, it is possible more columns get added to
X later on. Also, it is possible the structure of
Diff gets revised in the future. Furthermore, all of this might need to be translated to a different SQL dialect once other DB vendors are targeted.
EDIT: As an alternative for the full outer join, I could also use an inner join there and detect
X rows that are only present for either
o2 separately. Not sure if that would be better performance-wise; at least, it would remove the necessity to suppress the column-based comparisons when only one object was found, which the current source code I posted does not do yet.
EDIT2: To repeat, and clarify: The
Diff table must receive a row for each difference among a pair of equivalent
X rows. Not just one row for the first detected difference, but one row for each detected difference. If the two
X rows have different values in columns
C, I two rows must be created in
Diff with the appropriate messages. This is the really tricky part, and the one aspect that means I cannot just filter the set of