VBA, HELP comparing table data....

Roy Harve
#1 / 3

HELP comparing table data....

Quote:

>I have two tables. One is a temp table that is imported by a user. It
>contains the same layout as the one in the database. I want an easy way to
>compare the data. The premise is that one user can export their data and
>another can upload it into their table.

Seems reasonable enough so far.

Quote:

>I want to test each field in each
>row and then delete the new data (temp table) if it is different. The
>remaining recordset will be appended with Select Into.

This seems backward. If the new data is different from the old, you
delete the new data? Then append the new data that is really not new?
And SELECT INTO only creates new tables, so it can't append at all.

I think there are four situations that come up in the comparison. If
you explain what you want to do with each one I am sure you will get
some answers.

1) Rows in the Temp version that are not in the Prod version. Do you
want to INSERT them into PROD?

2) Rows in the Prod version that are not in the Temp version. Do you
want to keep them around, or delete them?

3) Rows in both Temp and Prod with differences in non-key columns. Do
you want to UPDATE these rows in Prod? DELETE them from PROD and then
INSERT them from Temp?

4) Rows in both Temp and Prod with no differences in non-key columns.
Do you want to just keep them around?

Roy

Sun, 30 Jun 2002 03:00:00 GMT

RL
#2 / 3

HELP comparing table data....

Quote:

>1) Rows in the Temp version that are not in the Prod version. Do you
>want to INSERT them into PROD?

>2) Rows in the Prod version that are not in the Temp version. Do you
>want to keep them around, or delete them?

>3) Rows in both Temp and Prod with differences in non-key columns. Do
>you want to UPDATE these rows in Prod? DELETE them from PROD and then
>INSERT them from Temp?

>4) Rows in both Temp and Prod with no differences in non-key columns.
>Do you want to just keep them around?

>Roy

1. Yes
2. Keep
3. Yes. Either update or delete and then insert (or both) which ever is
easier.
4. Keep them in the Prod.
the Temp table will be dropped after the data is inserted from it.

Thanks!

Mon, 01 Jul 2002 03:00:00 GMT

Roy Harve
#3 / 3

HELP comparing table data....

RL,

This is what you should need:

UPDATE Prod
SET nonkeycol1 = T.nonkeycol1,
nonkeycol2 = T.nonkeycol2,
...
nonkeycoln = T.nonkeycoln
FROM Test as T
WHERE Prod.keycol = T.keycol
AND (Prod.nonkeycol1 <> T.nonkeycol1
OR Prod.nonkeycol2 <> T.nonkeycol2
...
OR Prod.nonkeycoln <> T.nonkeycoln)

INSERT INTO Prod
SELECT *
FROM Test
WHERE NOT EXISTS(select * from Prod as P
where T.keycol = P.keycol)

An alternative pair of commands to accomplish the same thing:

DELETE FROM Prod
WHERE EXISTS(select * from Prod as P
where T.keycol = P.keycol)

INSERT INTO Prod
SELECT *
FROM Test

Roy

Mon, 01 Jul 2002 03:00:00 GMT