no

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

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

rdbms 1909343931327406547

Post a Comment Default Comments

Outsourcing

Are you looking for freelancers in the Philippines? Get in touch.

Donations

If you like what I do, you can support this channel by buying me a coffee. I would be grateful for your contribution! Your donations will help me create more FREE online courses and learning materials for programmers and remote workers.

item