I have two tables of the same structure, ID and Location. I would like
to run a SQL query and end up with two identical tables. Adding
Locations missing in one to the other and vice-versa.

Eg.

Table1
a
b
c
d

Table2

b
c
d
e

After the SQL both tables should be:

a
b
c
d
e

I know I could do it through code but I'd like to brush-up on my SQL.

TIA

Requires TWO append queries

INSERT INTO Table1 (ID, Location)
SELECT Table2.ID, Table2.Location
FROM Table2 LEFT JOIN Table1
ON Table2.ID = Table1.ID
AND Table2.Location = Table1.Location
WHERE Table1.Id is Null

AND

INSERT INTO Table2 (ID, Location)
SELECT Table1.ID, Table1.Location
FROM Table1 LEFT JOIN Table2
ON Table1.ID = Table2.ID
AND Table1.Location = Table2.Location
WHERE Table2.Id is Null


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 12/20/2010 3:32 PM, ck wrote:

ck wrote:

Just run two append queries based on an umatched Select
statement:

INSERT INTO tbl1 (<field list>)
SELECT <field list>
FROM tbl2 LEFT JOIN tbl1
ON tbl2.keyfied = tbl1.keyfield
WHERE tbl1.keyfield Is Null

INSERT INTO tbl2 (<field list>)
SELECT <field list>
FROM tbl1 LEFT JOIN tbl2
ON tbl2.keyfied = tbl1.keyfield
WHERE tbl2.keyfield Is Null

<field list> represents the list of fields you want to copy
and keyfield represents the field (or fields) needed to
determine if the record exists in the other table.

--
Marsh