How to "merge" two tables

[martinmike2]

I have a table that I d/l from my higher-ups on a monthly basis that I
need to incorporate into my current application. Basically what needs
to happen, is I need to append out duplicates present in the new
table, then somehow merge it into an existing table, only replacing
new data.

I have tried using an update query for this, but it removed all 748 of
my employees and inserted a single record 748 times. Any ideas would
be helpful.

 

[Tom van Stiphout]

On Mon, 14 Jul 2008 04:09:47 -0700 (PDT), martinmike2

An update query updates existing records. Why did you choose that?
You write that you want to "append out" and "replace new data". Use
precise language: that's not a verb, and new data cannot be replaced
because it does not exist in the first place.
I speculate you want to leave existing records in place, and add new
records.
First thing to decide is what constitutes an "existing record".
Hopefully there is some unique ID value such as EmployeeID or OrderID.
If not perhaps it is some combination of fields such as
EmployeeLastName plus EmployeeBirthdate. That combination of fields
should have a unique index in the database design.
Then build an append query with the WHERE NOT IN clause. Something
like:
INSERT INTO DestinationTable ( fields_list )
SELECT fields_list
FROM SourceTable
WHERE UniqueID NOT IN (select UniqueID from Destination Table)
(of course all the names need to be changed to objects in your
database)

-Tom.

 

[martinmike2]

On Mon, 14 Jul 2008 04:09:47 -0700 (PDT), martinmike2


An update query updates existing records. Why did you choose that?
You write that you want to "append out" and "replace new data". Use
precise language: that's not a verb, and new data cannot be replaced
because it does not exist in the first place.
I speculate you want to leave existing records in place, and add new
records.
First thing to decide is what constitutes an "existing record".
Hopefully there is some unique ID value such as EmployeeID or OrderID.
If not perhaps it is some combination of fields such as
EmployeeLastName plus EmployeeBirthdate. That combination of fields
should have a unique index in the database design.
Then build an append query with the WHERE NOT IN clause. Something
like:
INSERT INTO DestinationTable ( fields_list )
SELECT fields_list
FROM SourceTable
WHERE UniqueID NOT IN (select UniqueID from Destination Table)
(of course all the names need to be changed to objects in your
database)

-Tom.





- Show quoted text

I'll give that a try, but what is happening is:

We get a table on a monthly basis with both new employees and updated
information concerning current employee's. What we need to have
happen is that the new table containing the new and updated data needs
to be incorporated into the existing personnel table so it will update
the records of our current employees and add records for the new
employees. All of this "new" data is contained in the new table, and
the working table has all the current data.

I am trying to make this "fool proof" so that a lay-person can do this
"update".

 

[Tom van Stiphout]

On Mon, 14 Jul 2008 06:44:04 -0700 (PDT), martinmike2

Thanks for clarifying.
You will need to run two queries. One is the append query I described,
which will add new employees. Another one is an Update query. That one
needs a join between the SourceTable and the DestinationTable on
EmployeeID. That will prevent all the records to get the same value.

Indeed ideally this can all be run in code behind a form, so the only
thing the user has to do is identify the Import file (using File/Open
dialog) and click a button to run the two queries.

-Tom.