Insert Statement

Hi all,
I have an insert statement as follows:
INSERT INTO STAGE.SMMT_Archive
SELECT *
FROM STAGE.smmt_working W
WHERE (NOT EXISTS
(SELECT *
FROM stage.smmt_archive A
WHERE A.RegMark = W.Regmark AND
A.Vin_Number = W.Vin_Number))
Basically it should look at one table and only insert if there is
uniqueness I have also changed the receiving table to have a unique
index too.
Does this work? Will I now only get unique data items in my archive
table?
And yep I'm new to this and this is as far as I've got using the help!
Thanks
SamSam,
your statement works for your purpose.
There is, btw, another way of doing it with better performance:
INSERT INTO STAGE.SMMT_Archive
SELECT W.*
FROM STAGE.smmt_working W
LEFT OUTER JOIN stage.smmt_archive A
ON A.RegMark = W.Regmark AND
A.Vin_Number = W.Vin_Number
WHERE A.A_nonNull_Column is null
It's always a good idea to test your code, from here or anywhere.
hth
Quentin
"SG" <sgpgpjr@.yahoo.ie> wrote in message
news:fe3ac5dd.0311110551.10bf84c4@.posting.google.com...
> Hi all,
> I have an insert statement as follows:
> INSERT INTO STAGE.SMMT_Archive
> SELECT *
> FROM STAGE.smmt_working W
> WHERE (NOT EXISTS
> (SELECT *
> FROM stage.smmt_archive A
> WHERE A.RegMark = W.Regmark AND
> A.Vin_Number = W.Vin_Number))
> Basically it should look at one table and only insert if there is
> uniqueness I have also changed the receiving table to have a unique
> index too.
> Does this work? Will I now only get unique data items in my archive
> table?
> And yep I'm new to this and this is as far as I've got using the help!
> Thanks
> Sam