Tech-Today

Moving data from a table in one database into another with the same table definition

Objective: Given the same schema of database for example an active and archive, how should we move rows of data from one table in one database into the same table name in another database.

Below is a java code snippet/pseudocode that I've created:


public void processUpload(DatabaseTables tables, IDatabaseClient activeDatabase, 
 IDatabaseClient archiveDatabase) throws SQLException {
//loop to each table names
foreach(DatabaseTable table : tables) {
 List listInserts = new ArrayList();
 String tableName = table.getName();

 String sqlSelect = String.format("SELECT * FROM %s", tableName);
 //query the active database
 ResultSet rs = activeDatabase.querySql(sqlSelect);

 //get the column names
 StringBuilder sbColName = new StringBuilder();
 ResultSetMetaData rsmd = rs.getMetaData();
 for(int i = 1; i <= rsmd.getColumnCount(); i++) {
  sbColName.append(rsmd.getColumnName(i));
  sbColName.append(",");
 }
 sbColName = sbColName.delete(sbColName.length() - 1, sbColName.length());

 //get row values
 StringBuilder sbValues = null;
 while(rs.next()) {
  sbValues = new StringBuilder();
  for(int i = 1; i <= rsmd.getColumnCount(); i++) {
   if( rsmd.getColumnType(i) == Types.INTEGER || 
  rsmd.getColumnType(i) == Types.NUMERIC) {
    sbValues.append(rs.getObject(i));
   } else {
    sbValues.append("'" + rs.getObject(i) + "'");
   }
 
   sbValues.append(",");
  }
  sbValues = sbValues.delete(sbValues.length() - 1, sbValues.length());
  listInserts.add(String.format("INSERT INTO %s (%s) VALUES (%s)", 
        tableName, sbColName.toString(), sbValues.toString()));
 }
 //insert into the archive database
 archiveDatabase.executeSql(listInserts);
}
}

Note:
IDatabaseClient - is the interface of our model class

- Mssql How To Update A Column Base On A Row Number
Just recently I was asked to normalized or divide a table into 2 tables. Unfortunately, the only column that linked them (email) allows multiple same values, even aggregate would not do (email, password, etc). This is because there's a field that...

- Android Bundle Sqlite Database On Application. Create Sqlite Database On First Application Invoke.
How to setup a default sqlite database structure with data in an android application. Add this class and initialize it in your main class that extends Activity. import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase;...

- Mysql Engine: Myisam Vs Innodb
MyISAM (http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html) -to create a MyISAM table, add ENGINE = MYISAM; at the end of create table script -supports table locking (this is an issue when you need to backup the whole database) -fast execution...

- Operation Must Use An Updateable Query
I am currently developing a certain application in MS Access and I need to link 2 databases, select data from one and move it to the second. In access I have no problem using the TransferDatabase command. DoCmd.TransferDatabase acLink, "Microsoft Access",...

- Magento Sqlstate[hy000] [2013] Lost Connection To Mysql Server At 'reading Initial Communication Packet', System Error: 111
After I have successfully dumped my database in our hosting server, I've encountered the above error when I open the magento page. Solution: 1.) Open the file, app/etc/local.xml, and look for the following line: <default_setup> <connection>...

Tech-Today