DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Fyodor Kupolov

Fyodor has posted 14 posts at DZone. View Full User Profile

Copy Table From One Database To Another

02.13.2007

| 24263 views |

        This <a href="http://scriptella.javaforge.com">Scriptella ETL</a> script copies all rows from <b>Src_Table</b> to <b>Dest_Table</b>.
<b>Src_Table</b> contains the following columns: <b>id, first_name, last_name</b>
<b>Dest_Table</b> contains the following columns: <b>id, name</b>
The <b>name</b> column of the Dest_Table is produced by a concatenation of <b>first_name</b> and <b>last_name</b> from the <b>Src_Table</b>
This example demonstrates HSQLDB-To-Oracle copy procedure, although it works between virtually any databases.

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
  <connection id="in" driver="hsqldb" url="jdbc:hsqldb:file:demo" 
              classpath="hsqldb.jar" user="sa"/>
  <connection id="out" driver="oracle" url="jdbc:oracle:thin:@localhost:1521:ORCL" 
              classpath="ojdbc14.jar" user="scott" password="tiger"/>
  <!-- Copy all table rows from one to another database -->
  <query connection-id="in">
      SELECT * FROM Src_Table --Selects all rows
      <!-- For each row executes insert -->  
      <script connection-id="out"> 
          INSERT INTO Dest_Table(ID, Name) 
          VALUES (?id,?{first_name+' '+last_name})
      </script>
  </query>
</etl>

Tags: