Eduard Lebedyuk · Oct 25, 2017

Merge of two databases

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?

0 376
Discussion (5)0
Log in or sign up to continue

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

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,


Thank you, Robert!

That's what I'm looking for.

Hello, Amir!

Can ECP be configured in the case where one of the server is on the internal network? So server1 can see and get replies from server2 but server2 can't access server1 by itself?

ECP always acts as Master/Slave relation.
Server 2 holds the "FROM" DB as ECP master while Server1 pulls it down as ECP client.

So from logic Server2  just can "reply" to requests from Server1.
Though I'm not sure  how a firewall in between has to be configured.