make tables the same

[syodb]

got db.mdb\tableA and db.mdb\tableB. Like to merge them, A->B and B->A, make them the same so that they both have same records. what is best query to do so?.
table A & B have exact same fields.:
ID name1 name2 mydate mytime name3 nam4

 

[June7]

Link table A to db B. Run INSERT SELECT action query.

Link table B to db A. Run INSERT SELECT action query.

However, if tables use autonumber primary key and there are dependent tables, this gets a lot more complicated.

Why do you need two db with same dataset?

 

[The_Doc_Man]

To amplify June7's comment and add a detail or two:

IF both table A and table B have the same fields AND those fields have a unique index AND we are not talking about an autonumber as the unique field, then you can use an INSERT INTO from A to B and then do the same from B to A, ignoring key errors both times.

HOWEVER, if you just did that INSERT INTO from A to B and then delete A and copy B to become a new A, you would get the same result. The issue then is, once they are the same, to use them differently might represent a specific and subtle type of table denormalization.

INSERT INTO tableB (ID, name1, name2, mydate, mytime, name 3, nam4 ) SELECT ID, name1, name2, mydate, mytime, name3, name4 FROM tableA ;

HOWEVER - if ID is the same VALUE between both tables and is also an autonumber, you might have key violations. If the ID values match from table A and table B, does that guarantee that the records were the same? If so, then the above would become

INSERT INTO tableB (ID, name1, name2, mydate, mytime, name 3, nam4 ) SELECT ID, name1, name2, mydate, mytime, name3, name4 FROM tableA WHERE ID NOT IN (SELECT ID FROM TableB ) ;

 

[syodb]

The ID is an autonumber. Can it be done based on the data and time?. Like do insert if the name1, name2, mydate, mytime in source table does not exist in destination table. How that would look like?. There are no dependent tables on the ID.

 

[The_Doc_Man]

You would leave out the ID field from the INSERT INTO. Then you would have to decide if you have any candidate secondary unique key. Date/Time might work though it would take some exploring. Date/Time plus one or two of the names might work but again you would have to test for uniqueness.

Don't take this as me being a nit-picker, but this design seems to have painted you into a corner. Two identically structured tables with autonumber primary keys in the same database AND those tables can be meaningfully merged - seems like something is redundant somehow. Something is missing here - a reason for having what appears to be duplicate structures. The NORMALIZED way to do this is to have ONE table with those fields - plus one more field that, in the absence of a better way to do this, says the record came from source "A" or source "B."

 

[CJ_London]

you may also need extra queries where same ID but different field values - they will require an update query, but you need to decide which is the correct value

 

[isladogs]

You might also find it useful to read this article on my website Synchronising data

 

[syodb]

All hints and suggestions are so helpful.
isladogs, the link is so awesome.

 

[isladogs]

We are all glad to help. Hopefully you have the answers you need