Compare like data

[GitarJake]

Hello all,

I am new to sql and have some Access experience.

In sql, how do I: compare 2 identical tables, (except for data); then update
table 1 with new data from table 2

TIA

Jake

[Erland Sommarskog]

GitarJake (gi*******@spammersuntied.com) writes:

I am new to sql and have some Access experience.

In sql, how do I: compare 2 identical tables, (except for data); then
update table 1 with new data from table 2

To find all rows that are different, assuming that the key is keycol:

SELECT *
FROM a
FULL JOIN b ON a.keycol = b.keycol
WHERE a.keycol IS NULL OR
b.keycol IS NULL OR
(a.col1 <> b.col1 OR a.col1 IS NOT NULL AND b.col1 IS NULL OR
a.col1 IS NULL AND b.col1 IS NOT NULL) OR
(a.col2 <> b.col2 OR a.col2 IS NOT NULL AND b.col2 IS NULL OR
a.col2 IS NULL AND b.col2 IS NOT NULL) OR
...

For columns that does not permit NULL, you can skip the checks for NULL.

To update:

UPDATE a
SET col1 = b.col1,
col2 = b.col2,
..
FROM a
JOIN b ON a.keycol = b.keycol
WHERE (a.col1 <> b.col1 OR a.col1 IS NOT NULL AND b.col1 IS NULL OR
a.col1 IS NULL AND b.col1 IS NOT NULL) OR
(a.col2 <> b.col2 OR a.col2 IS NOT NULL AND b.col2 IS NULL OR
a.col2 IS NULL AND b.col2 IS NOT NULL) OR

DELETE a
WHERE NOT EXISTS (SELECT *
FROM b
WHERE b.keycol = a.keycol)

INSERT a (keycol, col1, col2, ...)
SELECT keycol, col1, col2, ...)
FROM b
WHERE NOT EXISTS (SELECT * FROM a WHERE a.keycol = b.keycol)

You can take some shortcuts here. The simplest way is to say "DELETE a"
and then insert all from b. The long where condition on the UPDATE
statement can be excluded, you only update a few extra rows with the
values they already have.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp