creating a matching program

Hi,

I'm just looking for some general advice on how to approach something.

I have two tables A and B, containg common fields of product, sales
date and qtyo.
Some records contain the same data and I want to dump them in a new
table called C and leave Tables A and B containg only data that
doesn'r match.

How do i go about approaching this??

Regards,
CiarnOn 22 Nov 2004 02:18:39 -0800, Ciar?n wrote:

>Hi,
>I'm just looking for some general advice on how to approach something.
>I have two tables A and B, containg common fields of product, sales
>date and qtyo.
>Some records contain the same data and I want to dump them in a new
>table called C and leave Tables A and B containg only data that
>doesn'r match.
>How do i go about approaching this??
>Regards,
>Ciarn

Hi Ciarn,

INSERT INTO C
SELECT A.Column1, A.Column2, ..., A.ColumnN
FROM A
INNER JOIN B
ON B.Column1 = A.Column1
AND B.Column2 = A.Column2
......
AND B.ColumnN = A.ColumnN

DELETE FROM A
WHERE EXISTS (SELECT *
FROM C
WHERE C.Column1 = A.Column1
AND C.Column2 = A.Column1
.......
AND C.ColumnN = A.ColumnN)

DELETE FROM B
WHERE EXISTS (SELECT *
FROM C
WHERE C.Column1 = B.Column1
AND C.Column2 = B.Column1
.......
AND C.ColumnN = B.ColumnN)

Enclose this all in a transaction, add error handling and you're set.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Cheers Hugo