Append only different rows from one table to another 2

[NCBrit]

Hi,

I want to append a table with rows from another table, but only if the rows in the 2nd table do not exist in the first. Is this possible?

Thanks.

 

[rac2]

Sure. It is easy if you have a column in both tables with the same value that uniquely identifies each row. Examples might be social security number, employee id, checking account number, telephone number. In that case

INSERT bigTable (ssn, name, date_retirement)
SELECT ssn, name, date_retirement
FROM smallTable
WHERE ssn NOT IN ( SELECT ssn FROM bigTable )

You might have a few columns that match up to identify the rows as representing the same thing. An example might be director, year_released, title.

INSERT mainTable (director, year_released, title, studio, academy_awards)
SELECT director, year_released, title, studio, academy_awards
FROM otherTable o
LEFT JOIN mainTable m ON
            m.director = o.director
            AND m.year_released = o.year_released
            AND m.title = 0.title
WHERE m.movie_id IS NULL

This JOINS the tables on enough columns to establish that they match. The LEFT JOIN provides a row for every row in the otherTable regardless of a match in the mainTable. Any row that does not have a match will have a value of NULL for every column in the mainTable. I am assuming there is some column which is a unique id, m.movie_id, but any column that would not have a valid value of NULL would do.

You might be hoping that there is some function or facility in Access or in relational databases that finds the missing rows (Excel has or used to have such a function I think). Unfortunately, there is no such animal. At least I dont know of it.


CAUTION. Always run the SELECT part of these statements first to check that it yields exactly the rows you want to insert. It is kind of difficult to reverse an INSERT.

 

[BNPMike]

Just append them all. Access will reject the duplicates.

 

[OhioSteve]

There are many ways to do this. My example uses these tables~

table1
abc (text)
xyz (text, primary key)

table2
abc (text)
xyz (text, primary key)

The goal is to append records from table2 to table1, but only if table1.xyz does not match table2.xyz.

I would use two queries.

SELECT
table2.abc,
table2.xyz,
Table1.xyz
FROM Table1
RIGHT JOIN table2 ON Table1.xyz = table2.xyz
WHERE (((Table1.xyz) Is Null));

INSERT INTO Table1 ( abc, xyz )
SELECT mySelectQuery.abc, mySelectQuery.table2.xyz
FROM mySelectQuery;

This works because the first query's join condition displays everything in table2, but only the matching values in table1. So in that query, Table1.xyz will be null if a match does not exist.

 

[rac2]

BNPMike-

Access 2000 is perfectly happy to append duplicate rows.

I wonder what leads you to say that Access will reject the duplicates?

 

[BNPMike]

if the rows in the 2nd table do not exist in the first.

Access is relational. A relation must have a key. As a rule declare that key to Access. That gives you a unique index. Access will reject attempts to add records with an existing key.

If you can still add something you think "exists in the first" then you've got something wrong somewhere.

 

[rac2]

Yes. I was careful not to assume that our friend had a well designed database. No offense intended. But you have to wonder how the situation would arise where one has two tables for the same things, but with different rows. Maybe he is importing data, or changing the design.

Would not Access fail on the first row encountered with a duplicate key? I dont know, I dont work much with access, but MS SQL Server would fail and that leaves the problem unsolved.

 

[NCBrit]

Thanks rac3. I got it to work using your code:

INSERT bigTable (ssn, name, date_retirement)
SELECT ssn, name, date_retirement
FROM smallTable
WHERE ssn NOT IN ( SELECT ssn FROM bigTable )

I am importing data as you say, so that is why this situation arose. I am duplicating one customers data from a customer level table, into a dealer level table where it will apply for all customers of one dealer if that makes sense. However I have to be careful not to overwrite any existing rows.

 

[BNPMike]

rac2
Access does not fail. It will simply report the number of rows rejected but will add the others.

The subquery solution you have will work for a few rows, but if you needed to do a million you would find it much quicker to let Access do the work.

 

[MichaelRed]

No mention of "Update" query? If the dest table has a unique index on a common field, it works w/o any issues, except (of course) also changing any data in the existing records.




MichaelRed
mlred@verizon.net