New post
Question
· Oct 25, 2017

Merge of two databases

#Databases #Globals #System Administration #Caché

I have two databases, and both of them have a global ^Data with subscripts going from 1 to 10 000 000.

I want to get one database with global ^Data but with subscripts going from 1 to 20 000 000.

Since each db is around 100 Gb and they are on a different hosts, what's the best way to merge them?

Discussion (5)0
Log in or sign up to continue
Robert Cemper · Oct 25, 2017

Assumimg you have both DB on the same instance but different namespaces "FROM" and "TO"

You may run a loop like this

set id=""
for cnt=1:1 {
  set id=$ORDER(^|"FROM"|Data(""),1,value) quit:id=""
  set ^|"TO"|Data(id+10000000)=value)
  if cnt#100000 write cnt,?10,id,!
}

For the connections of the host you may use ECP

For a more structured global you might need to use $QUERY()

The write is just to see progress

3 0
Amir Samary · Oct 25, 2017

Hi Edouard!

Robert's solution works perfectly if you map the other database, through ECP, to the other host. ECP is very simple to configure. 

If this global belongs to a table, you could configure a ODBC/JDBC connection to the other system and created a linked table on system "TO" that is linked through ODBC/JDBC to the real table on system "FROM". And run a code similar to Robert's. But instead of an $Order, you would use %SQL.Statement and SELECT the records. 

ECP requires a Multi-Server license. That is why I am suggestion this alternative with SQL Gateway.

Kind regards,

AS

1 0