ADO - merge 2 databases

[sunaj]

Hi,

I have two databases with the same tables and would like to merge them into one database.

Do you know the best way to do this? (I think this question has been aksed before, but I can't find it...)

I use VB 6.0 and ADO 2.5.

Thanks, Sunaj

 

[link9]

My admittedly sophmoric approach to this would be:

1)Create two recordsets -- one with data from the first table, and the other with data from the second --

2)Jump in a loop, and start to .addnew to one recordset with data from the other --

Continue until otherRS.eof, and then call .update on the one that you were adding to

rs1.open "SELECT * FROM table1", con1, 2, 1
rs2.open "SELECT * FROM table2", con2, 2, 3

while not rs1.eof
rs2.addnew
rs2("field1&quot = rs1("field1&quot
rs2("field2&quot = rs1("field2&quot
rs1.movenext
wend

rs2.update

 

[sunaj]

Hi,

Yes I considered this approach, but I would have to check if the first table allready contains the item that I'm trying to insert:

-----------------------------------------------------------
rs1.open "SELECT * FROM table1", con1, 2, 1
rs2.open "SELECT * FROM table2", con2, 2, 3

while not rs1.eof
TmpRs.movefirst
TmpRs.find "field1='"&rs1.fields(0)&"' AND field2='"&rs1.fields(1)&"'"
if TmpRs.eof then
rs2.addnew
rs2("field1&quot = rs1("field1&quot
rs2("field2&quot = rs1("field2&quot
endif
rs1.movenext
wend
rs2.update
-----------------------------------------------------------

This should work, but my databases are quite large and this approach is going to take forever.

Can anybody think of a smarter way?
Sunaj

 

[MichaelRed]

Create the recordsets of each of the databases. Generate a UNION query for each unique recordset. Generate a Maketable query based on each Union Query.

Actually, with just a small bit of code, the whold thing could be done in a loop with ONE union query and ONE MakeTable Query. Of course, both of htese queries would need to be dynamically re-written / modified for each recordset pair / group.


instantiate the databases (db1, db2)

For each loop to "list" each table which exists in both dbs.
(I would put these in a string array)

For loop of the 'matching' tables.
Check the field names and data types 'match'. If TRUE, the construct the Union Query as a string. Assign the sql statement to the SQL property 'dunmmy' query.

Write another SQL statement as a string, using the 'dummy' Union Table as the source and the table name (from the array already done) as the destination.

Loop till done.




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[sunaj]

Hi MichaelRed,

I can see what you mean, but I think I need a little more help on how to implement it, please.
How can you do a UNION between 2 tables in 2 different databases (between 2 recordsets?)?
And the MakeTable query... how does that work?
Do I need to make a 3rd database or can I do the UNION in one of the 2 existing databases?

Below I've pasted code that loops through all the fields in all the tables. It works.

I appreciate the help,
Sunaj

----------------------------------------------------------------
Dim con1 As New ADODB.Connection, catNew As New ADOX.Catalog
Dim con2 As New ADODB.Connection
Dim ConStr1 As String, ConStr2 As String
Dim i As Integer, j As Integer
Dim dummy


ConStr1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =C:\tmp\test1.mdb"
ConStr2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =C:\tmp\test2.mdb"
con1.Open ConStr1
con2.Open ConStr2
catNew.ActiveConnection = ConStr1

For i = 0 To catNew.Tables.Count - 1
If Left(catNew.Tables(i).Name, 4) <> &quot;MSys&quot; Then
For j = 0 To catNew.Tables(i).Columns.Count - 1
'Merge here...

Next j
End If
Next i
con1.Close: con2.Close
Set catNew = Nothing

 

[MichaelRed]

I can see what you mean, but I think I need a little more help on how to implement it, please. W/o getting into details, I can only really offer 'strategies', but I can go a lIttle further.


How can you do a UNION between 2 tables in 2 different databases (between 2 recordsets?)? I do not think this is really a problem. When you instantiate the individual tables, they 'exist' within the ADO environment just as recordsets. A Union query - like the individual table recordsets can exist in either (or neither) of the &quot;databases&quot;. After all, that is (one of) the supposed advantages of ADO. You should be able to make a recordset out of almost anything (even a CSV text file),


And the MakeTable query... how does that work? It just start off with &quot;Insert Into&quot; instead of &quot;Select&quot;. You should be able to read about make table queries in any number of help files (Ms. Access, or most any other SQL database help / Reference).


Do I need to make a 3rd database or can I do the UNION in one of the 2 existing databases? Actuall, no database is REQUIRED, you can actually &quot;Save&quot; and ADO recordset to disk w/o placing it in a database, however (like the Tic-Tac commercial) &quot;I wouldn't reccomend it&quot;. Which database you choose to place the table in really depends on WAY to much detail, such as what (if any) other objects exist in the source databases and wheather you want to retain these (or even which ones from the seperate database). This is an exercise to be undertaken by Lady/Sir &quot;BraveHeart&quot;. If there is ANYTHING in the source databases other than the data, SOMEONE will not be happy with your choices / selections re what to retain.

My 'knee jerk' response would be to create a NEW (THIRD) database. This would permit you to retain the original names of the tables and to selectively copy / import other objects from the sources w/o loseing any of someone's 'favorite little enhancements'. Of course, if these are DATA only dbs, it doesn't matter.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over