How to Collete a database?

How to Collete a database? What is the code?

Example:

I had have two database, and the two database have two table which are similiar same but the data are some different. It mean, the two table should have the same data, but there are miss another one. How to make the data same to each other in the two table?

Thanks.If I understood you well, the solution might be very simple or impossible.

Simple solution: you KNOW which of two tabes contains correct data. Delete all "wrong" data from the table and insert "correct" data into it.

(Almost) impossible solution: correct data can be found in both tables. Do you know which of them are correct and which are wrong? If so, you could create third table and insert only correct data from both tables into it; delete all data from original tables; insert data from third table into original ones.

Impossible solution: if there's no obvious WHERE clause which distincts correct records (or even columns?) from wrong ones, you're in deep trouble.|||no... i think you misundestand my meaning

example:
table1 have 1,2,4,9...
table2 have 1,3,5,7,8,....

and i want be like this:
table1 have 1,2,3,4,5,7,8,9...
table2 have 1,2,3,4,5,7,8,9...

so, how?????

thanks.|||I see ... syntax might be like this (it may differ regarding your DB engine):

CREATE TABLE temporary_table AS SELECT * FROM first_table;

INSERT INTO first_table SELECT * FROM second_table;

INSERT INTO second_table SELECT * FROM temporary_table;

DROP temporary_table;|||i not understand...
i am beginner...
Please explain more... or give me the code more complete

thanks.|||What is your DB engine? When talking about "two databases" - are they really databases or are they schemas in the same database?

If those are different databases, you'll have to create some kind of a database link between them to be able to transfer data.
If we're talking about schemas in the same database, simple privilege grant would be enough.

Code I wrote IS the complete code; I don't know what to add. Perhaps just to say it in English?
- first create a temporary table to store data from the first table
- take all records from the second table and insert them into the first table. Now the first table contains all data you need.
- take all records from temporary table (actually, the original first table) and insert them into the second table. Now the second table contains all the records too.
- drop temporary table.|||I store one of the database in pendrive(in mdb format) and the other one in ms sql server.

However, I still do not know how to create a temporary table. But I also have a doubt, if "I take all records from the second table and insert them into the first table. Now the first table contains all data you need.", then the first table will have the repeating record.... I do not want the repeating record.

Thanks.|||Oh, of course you'll have repeating records! I missed that "1" :) Sorry!

OK then; here's another attempt: ("temporary table" is, in this case, created as selection of all record from some table). UNION will create distinct records from both tables:

CREATE temporary_table AS
SELECT * FROM first_table
UNION
SELECT * FROM second_table;

DELETE FROM first_table;
DELETE FROM second_table;

INSERT INTO first_table SELECT * FROM temporary_table;
INSERT INTO second_table SELECT * FROM temporary_table;

DROP temporary_table;

EDIT: This might be OK, I guess ... however, I've never heard of "pendrive(in mdb format)" you're talking about. Keep that in mind reading my code - it might not be usable on "pendrive" (whatever it is).|||Thank you very much.

After your teaching, I just realize have "union" thning. Pendrive (hardware) is a something to store file, same as external hardisk. However, I will try to do what you teach me.

Thank again.|||However, I have try but got many mistake, cause i am beginner, and not quick understanding sql.
below is my code:

Dim con As New ADODB.Connection
Dim objRS As New ADODB.Recordset
Dim conC As New ADODB.Connection
Dim objRSC As New ADODB.Recordset

Const lsDSN = "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.68.xxx,1433;" & _
"Initial Catalog=abc;" & _
"User ID=sa;" & _
"Password=axxxxxx"

Const lsDSNC = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=F:\MyLife.dat;" & _
"Jet OLEDB:Database Password=xxxxxx"

con.Open lsDSN
SQL = "SELECT * FROM History order by Dtime asc"
objRS.Open SQL, con, , 3

conC.Open lsDSNC
SQLC = "SELECT * FROM History order by Dtime asc"
objRSC.Open SQLC, conC, , 3

Create temporary_table
SQL
Union
SQLC

DELETE FROM SQL;
DELETE FROM SQLC;

INSERT INTO SQL SELECT * FROM temporary_table;
INSERT INTO SQLC SELECT * FROM temporary_table;

DROP temporary_table;

objRS.Close
Set objRS = Nothing
con.Close
Set con = Nothing

objRSC.Close
Set objRSC = Nothing
conC.Close
Set conC = Nothing

i using vb6 to create a *.exe. How to fix it?

Thanks.|||Unfortunately, I'm a tabula rasa regarding Visual Basic ... I'll be glad to help you further with SQL part of the solution, but let's hope someone else will know how to help you with this part of the problem.|||have a look at the COALESCE function.

SELECT COALESCE(t1.yourField, t2.yourField) as finalField
FROM t1 INNER JOIN t2 On t1.key = t2.key