no

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 databa...

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

Related

database 1909343931327406547

Post a Comment

item