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?
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.
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.
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
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;
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).
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;" & _
Const lsDSNC = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=F:\MyLife.dat;" & _
"Jet OLEDB:Database Password=xxxxxx"
SQL = "SELECT * FROM History order by Dtime asc"
objRS.Open SQL, con, , 3
SQLC = "SELECT * FROM History order by Dtime asc"
objRSC.Open SQLC, conC, , 3
DELETE FROM SQL;
DELETE FROM SQLC;
INSERT INTO SQL SELECT * FROM temporary_table;
INSERT INTO SQLC SELECT * FROM temporary_table;
Set objRS = Nothing
Set con = Nothing
Set objRSC = Nothing
Set conC = Nothing
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.