Need help composing SQL query

[adamr1001]

hi, here is what i'd like to do:

data from Table1 must be inserted into Table2.
the unique identifying field is called 'Genie'.
I'd like to insert all records from Table1 into Table2, except those whose Genie's already exist in Table2.
(in other words, records should NOT be overwritten in Table2). Thanks!

 

[tlbroadbent]

You can create a query to determine which records in table1 are not in table2 using the "Find Unmatched Query Wizard." Then you can convert that query to an append query. The code will be like the following.

INSERT INTO
Table2 ( Genie, col2, col3, col4 )
SELECT
Table1.Genie, Table1.col2,
Table1.col3, Table1.col4
FROM Table1
LEFT JOIN Table2
ON Table1.Genie = Table2.Genie
WHERE (((Table2.Genie) Is Null)); Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[adamr1001]

i got this from vbforums... would it work the same?

INSERT INTO Table2 (T2Field1, T2Field2, etc.)
SELECT T1.Field1, T1.Field2, etc.
FROM Table1 AS T1
WHERE NOT EXISTS
(SELECT 1 FROM Table2 AS T2 WHERE T2.Genie = T1.Genie)


thanks,
adam.

 

[tlbroadbent]

The VBForum query should also work. I prefer the JOIN query because it deals with relational sets. When dealing with large tables it may prove more efficient. However, I haven't run any benchmarks in Access to know if there is really any performance difference between the two query formats. Therefore, I recommend that you use whichever query does what you want and is understandable. I think many people find the VBForum query format is easier to understand. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous