title: Copying table from SQL Server into Oracle using JDBC
body: <p>I am trying to load three separate tables from SQL Server 2012 into Oracle. I have established connections to SQL Server and Oracle in separate classes as seen below:</p>
  
  <p><b>SQL Server connection:</b></p>
  
  <pre><code>public class TestSqlUtil {
      public Connection getConnection() throws SQLException, IOException
      {
          String propsFile = "tasmania.properties";
          Properties props = new Properties();
          InputStream inputStream = 
              this.getClass().getClassLoader().getResourceAsStream(propsFile);
  
          if (inputStream == null)
          {
              throw new FileNotFoundException("property file '" + propsFile
                  + "' not found in the classpath");
          }
  
          props.load(inputStream);
  
          String dblogin = props.getProperty("sqlfdb.LOGIN");
          String dbpasswd = props.getProperty("sqlfdb.PASSWD");
          String jdbcDrv = props.getProperty("sqlfdb.JDBCOCIDRV");
          DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
          Connection con = DriverManager.getConnection(jdbcDrv, dblogin, dbpasswd);
          System.out.printf("successfull connection");
          System.out.println();
          return con;
      }
      public void cleanUp(Connection con, PreparedStatement ps, ResultSet rs)
          throws SQLException
      {
          if (rs != null) rs.close();
          if (ps != null) ps.close();
          if (con != null) con.close();
      }
      public static void main(String a[]) throws SQLException, IOException
      {
          DaoUtil tasmaniaUtil = new DaoUtil();
          tasmaniaUtil.getConnection();
      }
  }
  </code></pre>
  
  <p><b>Oracle connection:</b></p>
  
  <pre><code>public class DaoUtil
  {
      public Connection getConnection() throws SQLException, IOException 
      {
          String propsFile = "tasmania.properties";
          Properties props = new Properties();
          InputStream inputStream = 
              this.getClass().getClassLoader().getResourceAsStream(propsFile);
  
          if (inputStream == null)
          {
              throw new FileNotFoundException("property file '" + propsFile
                  + "' not found in the classpath");
          }
  
          props.load(inputStream);
  
          String dblogin = props.getProperty("intfdb.LOGIN");
          String dbpasswd = props.getProperty("intfdb.PASSWD");
          String jdbcDrv = props.getProperty("intfdb.JDBCOCIDRV");
          DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
          Connection con = DriverManager.getConnection(jdbcDrv, dblogin, dbpasswd);
          System.out.printf("successfull connection");
          System.out.println();
          return con;
      }
      public void cleanUp(Connection con, PreparedStatement ps, ResultSet rs)
          throws SQLException 
      {
          if (rs != null) rs.close();
          if (ps != null) ps.close();
          if (con != null) con.close();
      }
  
      public static void main(String a[]) throws SQLException, IOException
      {
          DaoUtil tasmaniaUtil = new DaoUtil();
          tasmaniaUtil.getConnection();
      }
  }
  </code></pre>
  
  <p>I have stored all of the login details within a properties file.</p>
  
  <p>My first question is how would I combine these two classes to incorporate both connections? My second question would be how would do I use the select statement to grab all of the data from SQL Server and then the insert statement to insert into the Oracle Databases?</p>
  
  <h2>The tables in SQL Server and Oracle (from SQL Server to Oracle)</h2>
  
  <p><strong>1.    For MATERIAL_BATCH:</strong></p>
  
  <p><strong>In SQL Server:</strong></p>
  
  <pre><code>MATERIAL NUMBER|BATCH NUMBER|VENDOR BATCH NUMBER|VENDOR NUMBER|EXPIRATION DATE|
  MODIFIED_DATETIME 
  </code></pre>
  
  <p><strong>In SQL Developer:</strong></p>
  
  <pre><code>MATERIAL NUMBER|BATCH NUMBER|VENDOR BATCH NUMBER|VENDOR NUMBER|GOODS_SUPPLIER_NUMBER|
  EXPIRATION DATE|INSTIME 
  </code></pre>
  
  <p><strong>2.    For MATERIAL_MASTER:</strong></p>
  
  <p><strong>In SQL Server:</strong></p>
  
  <pre><code>PLANT|MATERIAL_NUMBER|MATERIAL_DESC|MODIFIED_DATETIME 
  </code></pre>
  
  <p><strong>In SQL Developer:</strong></p>
  
  <pre><code>PLANT|MATERIAL_NUMBER|MATERIAL_DESC|PROFIT_CENTER_NAME|STATUS|INSTIME  
  </code></pre>
  
  <p><strong>3.    For VENDOR:</strong></p>
  
  <p><strong>In SQL Server:</strong></p>
  
  <pre><code>VENDOR_NUMBER|VENDOR_NAME|MODIFIED_DATETIME 
  </code></pre>
  
  <p><strong>In SQL Developer:</strong></p>
  
  <pre><code>VENDOR_NUMBER|VENDOR_NAME|VENDOR_LOCATION|INSTIME
  </code></pre>
  
  <p>There are some fields that are not in SQL Server that are in SQL Developer (Oracle). For those I will keep <code>NULL</code>.</p>