How to Move Data from One Table to Another in Different Databases
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 databa...

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) {
ListlistInserts = 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
Post a Comment