Re: copy data from one database to another

Hi

I am trying to go about copying data from one database to another. The
source database is SQL server, and the destination is an Oracle
database. While I can access both database' fine, and insert into the
oracle database fine, I am having trouble copying data from the SQL DB
to the Oracle DB. What I was initially trying to do was copy all the
required data into a resultSet, and then transfer this into the Oracle
DB. But i probablt cannot insert this block of data in the resultSet
directly into the Oracle db i supposed??? If anyone could suggest a
better more efficient way to transfer the data i would be grateful.
Please see below a snippet of the code for transferring the data. For
the moment I am only copying rows between a certain timestamp. Its
seems to run fine, and I am not getting an error, but when I check the
Oracle database, there is no data in it.

        //setup the drivers
        DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver
());
        DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());

        //connect to the MySQL database
        Connection connSQL = DriverManager.getConnection(
        "jdbc:odbc:Owenreagh", "username", "password");
        System.out.println("connected to SQL DB successfully");

        //connect to the Oracle database
        Connection connOra = DriverManager.getConnection(
            "jdbc:oracle:thin:@hostname:1521:DBname", // URL
            "username", // username
            "password" // password
        );
        System.out.println("connected to Oracle DB successfully");

        Statement mySQLstat = connSQL.createStatement();
        Statement myOrastat = connOra.createStatement();

        ResultSet rsSQL = mySQLstat.executeQuery(
        "SELECT * FROM Owenreagh.dbo.T_WTG06_10MINDATA WHERE
((T_WTG06_10MINDATA.TTimeStamp>{ts '2009-01-01 00:10:00'}) AND
(T_WTG06_10MINDATA.TTimeStamp<{ts '2009-01-01 00:10:30'}))");

        ResultSetMetaData rsSQLmd = rsSQL.getMetaData();
        int numberOfColumns = rsSQLmd.getColumnCount();
    int max = numberOfColumns + 1;

        int index;
        while(rsSQL.next()){
            for(index = 1; index < max; index++) {
                myOrastat.executeQuery("INSERT INTO VESTASTURBINEDATA
VALUES" +
                                       rsSQL.getString
(index));
            }
        }