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,
Ciarán

On 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,
>Ciarán

Hi Ciarán,

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