Searching for data from one table in another table in t-sql (sql server2000) ?

Question

Hey.
I have table A. This table does not have any PK, it just stores lots of rows, which can only be identified by combination of its column values. There is procedure that takes data from table A, and from other tables, does proper matching/processing and feeds table B. Now, how do I check if data from table A is correctly inserted into table B?
It is sql server 2000 so EXCEPT is not a solution.

Maybe some procedure that would include:

1. cursor would fetch rows from table A ,
2. do select on B (with proper column matching)
3. and then if matching row has been found (select returned some data) increase counter (number of properly propagated rows)
4. if no matching row was found put data we were looking for into temporary table (for later review)

Update: Procedure that feeds table B doesn't put all rows from table A into table C. Additionally It also takes data from other table (let's call it C) and puts it into B (but also not all rows). I thought that maybe using one cursor to check B for data from A and then other cursor to check B for data from C would be good solution.

Answer 1

You can do a NOT EXISTS for rows that do not have a match according to your criteria

SELECT Columns
FROM TableA
Where NOT EXISTS
(
SELECT 1
FROM TableB
WHERE 1=1
AND TableA.Column1 = TableB.Column1
AND TableA.Column2 = TableB.Column2
AND TableA.Column3 = TableB.Column3
AND TableA.Column4 = TableB.Column4
)

You can do query for rows that have a match according to your criteria but do not have the rest of the data matching

SELECT Columns
FROM TableA
INNER JOIN TableB
    ON  TableA.Column1 = TableB.Column1
    AND TableA.Column2 = TableB.Column2
    AND TableA.Column3 = TableB.Column3
    AND TableA.Column4 = TableB.Column4
)
WHERE TableA.Column11 <> TableB.Column11
OR    TableA.Column12 <> TableB.Column12
OR    TableA.Column13 <> TableB.Column13
OR    TableA.Column14 <> TableB.Column14

Now that you have both sets of disconnected records, you can apply the necessary logic.

Most people here will give you SET based answers instead of CURSOR based answers. You will find a lot of material on StackOverflow regarding why not to use CURSORs.

Answer 2

Why do you need a cursor?

SELECT COUNT(*) --or simply the list of columns
FROM A LEFT JOIN B
          ON A.col1 = B.col1
             AND A.col2 = B.Col2
             AND ....
WHERE A.col1 IS NULL AND A.col2 IS NULL

You may need to specify several columns in the WHERE clause to check for NULL if the possibility exists that several of those columns could be NULL.

This may not be very fast, so depending on your index structure you may want to do a count first to check and see if you have any unmatched rows, and then do the search for rows.