Combine two tables

[krets]

I have two tables from different databases with exactly the same fields. (My database ended up being distributed a little too early and users began to add records before it was ready. Unfortunately the powers that be put it in two different spots on the network.)

Some of the records in the two databases are going to be identical (primary key = EmployeeId).

How would I go about creating a query to merge the two tables into one without creating any dupes?

 

[Remou]

Is employee id an autonumber or a number applied to employees by the company? If it is an autonumber, have you any employees with the same name?

 

[krets]

EmployeeID is a 9-digit number given to each employee by the company that will never duplicate for any two employees. (Until we reach 999,999,999 employees I suppose.)

 

[Golom]

What does "merge" mean exactly?

Clearly, you cannot have duplicate primary keys so how do you want to handle those records where EmployeeID is the same? Is one of the tables considered to be authorative and records coming from it will be preferentailly used when there is a duplicate? OR, perhaps, you want to determine which record to use based on the value of some other field (a timestamp for example)

If you need to incorporate ALL records from both tables into the merged table then you will need some mechanism to assign new, unique EmployeeID values to records that are currently duplicated.

 

[krets]

What I'd like to do is combine the two tables into one table with all records intact. The two tables will have the same core about about 600 records that are exactly the same. These are the records I was given when I developed the database.

However, since the database has been in two different places, users have added 20 to 50 records to each table that are different. So what I would like to do is combine the two tables merging all the newly added records into one table along with the initial 600 intact.

Does that make more sense?

 

[Remou]

Do you have a time stamp?

 

[Golom]

Sort of ... but there is still the primary key issue. The new merged table cannot have just the EmployeeID field as the primary key if that field is going to contain duplicates. You could do something like this

Select * INTO myNewTable

From

(
Select 'Table1' As SourceTable, T1.* From Table1 As T1
UNION ALL
Select 'Table2', T2.* From Table2 As T2
)

That will merge all the records with an extra field that identifies where the record originated. It doesn't however take care of the issue of duplicate EmployeeID records. They will still exist.

 

[krets]

Nope, no time stamp.

 

[krets]

Is there a way that the query can look at one "source" table and then grab only those records from the other table that have no match?

 

[Remou]

It seems to me that it might be easier to just skim the new records from the two tables by a match against your original 600. It would then be quite easy to sort through the 25-75 new records and add them back to the core 600.

 

[krets]

So would I use a delete/if type of query to accomplish that? (After backing up the tables of course.)