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

What have you tried so far? Are you familiar with Groovy's SQL object? - http://groovy.codehaus.org/api/groovy/sql/Sql.html — Michael Easter, Apr 07 '14 at 02:09
With groovy you will need a loop selecting each row from the source table and inserting it into the target table, which will work fine but is not very efficient. If you are dealing with any volume of data you will be much better of looking at the "unload" and "load" utilities. (Hint you can use groovy to generate the utility statements). — James Anderson, Apr 07 '14 at 03:54

score 2 · Answer 1 · answered Jan 07 '15 at 02:18

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)")
}

any idea how you'd go about writing this generically? – Steve Apr 23 '17 at 19:27 — Steve, Apr 23 '17 at 19:27

score 1 · Answer 2 · answered Jul 17 '18 at 21:27

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()

Copy db table from one server to another server using groovy

2 Answers2