Copy db table from one server to another server using groovy

Question

is there an easy way to query data from one server (select * from table_abc) and insert the result into a different server (insert into table_abc) ? Both server are using eg. db2, oracle etc. and tables containing various datatyps (varchar, varchar for bitdata, binary blobs etc.)

Cheers lza

Answer 1

Here is what worked for me...

import groovy.sql.Sql

// Establish the Connections
//Progress OpenEdge DB Connection 
def db1 = [url:'jdbc:datadirect:openedge://yourservername:20007; DatabaseName=demo;', user:'user1', password:'', driver:'com.ddtek.jdbc.openedge.OpenEdgeDriver']
def sql1 = Sql.newInstance(db1.url, db1.user, db1.password, db1.driver)
//SQL Connection (on local machine)
def db2 = [url:'jdbc:sqlserver://localhost;DatabaseName=TEST;', user:'sa', password:'abc123', driver:'com.microsoft.sqlserver.jdbc.SQLServerDriver']
def sql2 = Sql.newInstance(db2.url, db2.user, db2.password, db2.driver)

// Delete Stale Data in table 'NewPersons'
sql2.execute("delete from NewPersons where region='1'")

//Get data from sql1 connection 
sql1.eachRow("SELECT DISTINCT rtrim(First) AS FirstName, rtrim(Last) AS LastName FROM pub.persons WHERE reg ='1'")
{
//For each row of the sql1 connection insert the record into the sql2 connection
row -> while (row.next()) sql2.execute("INSERT INTO NewPersons(FirstN, LastN) VALUES ($row.FirstName, $row.LastName)")
}

Answer 2

The Dataset class can copy between tables without explicitly mentioning column names or types.

    def db1 = Sql.newInstance('jdbc url 1', 'user 1', 'password 1', 'driver 1')
    def db2 = Sql.newInstance('jdbc url 2', 'user 2', 'password 2', 'driver 2')
    def table1 = db1.dataSet('table name 1')
    def table2 = db2.dataSet('table name 2')

    table1.rows().each{ table2.add(it) }

    db1.close()
    db2.close()