HOW DO I COPY THE DATA FROM ONE SET OF TABLES TO ANOTHER

I HAVE TWO SET OF TABLES
String BKP_TableList[] = {"BKP_PLAYER_BOTS", "BKP_PLAYER_LOG", "BKP_PLAYER_LOGIC"
", "BKP_PLAYER_LOGIC_CALL", "BKP_PLAYER_MC_EVENTS", "BKP_PLAYER_MC_LINKS"
", "BKP_PLAYER_MC_NEWS" , "BKP_PLAYER_MUSIC", "BKP_PLAYER_NOTIFY_QUEUE"
", "BKP_PLAYER_OBJECTS" , "BKP_PLAYER_RECORDS_TO_PROCESS", "BKP_PLAYER_RECORD_OF_LOGIC_EV"
", "BKP_PLAYER_SEARCH" , "BKP_PLAYER_SOUND", "BKP_PLAYER_UMS_QUEUE"
", "BKP_PLAYER_VIDEO" , "BKP_PLAYER_WEBSITE"};
String MJST_TableList[] = {"MJST_PLAYER_BOTS", "MJST_PLAYER_LOG", "MJST_PLAYER_LOGIC"
", "MJST_PLAYER_LOGIC_CALL", "MJST_PLAYER_MC_EVENTS", "MJST_PLAYER_MC_LINKS"
", "MJST_PLAYER_MC_NEWS" , "MJST_PLAYER_MUSIC", "MJST_PLAYER_NOTIFY_QUEUE"
", "MJST_PLAYER_OBJECTS" , "MJST_PLAYER_RECORDS_TO_PROCESS", "MJST_PLAYER_RECORD_OF_LOGIC_EV"
", "MJST_PLAYER_SEARCH" , "MJST_PLAYER_SOUND", "MJST_PLAYER_UMS_QUEUE"
", "MJST_PLAYER_VIDEO" , "MJST_PLAYER_WEBSITE"};
I would like to copy from BKP_PLAYER_BOTS to MJST_PLAYER_BOTS, BKP_PLAYER_LOG to MJST_PLAYER_LOG and so on and so forth.
There is only one additonal column in the BKP_PLAYER_* i.e PLAYER_LEVEL, rest the structure is similar in both
the tables.
Is StoredProcedure suggested here for peformance reason since this involves copying of mass data from one set of
tables to another

I would use a stroed procedure and the commands I would look at are:
SELECT INTO or INSERT SELECT
I think the second is what you want and the syntax will look something like this:
INSERT MJST_PLAYER_BOTS SELECT * FROM BKP_PLAYER_BOTS
Bill

Hi Bill,
What I am looking at is code to copy the data from one set of tables to another.
Like , I have worked on something like this

try {
Properties dbprops = new Properties();
con = DriverManager.getConnection(sDBConnPool, null);
statement = con.createStatement();

for ( int i = 0; i < BKP_TableList.length; i++ ) {

resultset = statement.executeQuery("SELECT * FROM BKP_TableList["+i+"]");

rsmd = resultset.getMetaData();
int j = rsmd.getColumnCount();

sb.append("INSERT INTO MJST_TableList[i] VALUES (");

for (int k = 0; k <= j ; k++) {

String column = rsmd.getColumnName(k);

sb.append("'"+column+"'");

if ( k < j ) {
sb.append(",");
} else {

sb.append(")");
}

}

sb.append("SELECT * FROM BKP_TableList{i]");
sb.append("WHERE BKP_TableList[i].PLAYER_ID = MJST_TableList[i].PLAYER_LEVEL AND BKP_TableList[i].PLAYER_LEVEL='"+playerLevel+"'");
sb.append("AND PLAYER_ID='"+normalizePlayerID(playerId)+"'");

}

} // close of try
The only problem at this stage , I am facing is that it is giving this particular exception
Mon Apr 30 21:35:20 GMT+00:00 2001: <ServletContext-Servlets> weblogic.servlet.JSPServlet: Generated java file: /opt/devpkg/weblogic_instances/wl9000/classfiles/jsp/_SetPlayerState.java
ORA-00933: SQL command not properly ended
Well , At this stage I am just trying to debug the above code and I am not yet sure whether my approach is okay or not

Hi Janapareddy,
One tip I can give you: print out the SQL you generate! You would immediately have seen numerous things which are wrong with it. It would say things like "SELECT * FROM BKP_TableList[1]" (surely you would want the tablename there), "INSERT INTO MJST_TableList[i]" (ditto), single-quoted column names (making them strings), and so forth.
- Peter

Hi Peter,
I have tried to print out the query and I have commented out most of the other part of the code, However, I have been troubled by this particular exception again and again. I have no clues about it. Can you help me on this ?

String BKP_TableList[] = {"BKP_PLAYER_BOTS", "BKP_PLAYER_LOG", "BKP_PLAYER_LOGIC"
, "BKP_PLAYER_LOGIC_CALL", "BKP_PLAYER_MC_EVENTS", "BKP_PLAYER_MC_LINKS"
, "BKP_PLAYER_MC_NEWS" , "BKP_PLAYER_MUSIC", "BKP_PLAYER_NOTIFY_QUEUE"
, "BKP_PLAYER_OBJECTS" , "BKP_PLAYER_RECORDS_TO_PROCESS", "BKP_PLAYER_RECORD_OF_LOGIC_EV"
, "BKP_PLAYER_SEARCH" , "BKP_PLAYER_SOUND", "BKP_PLAYER_UMS_QUEUE"
, "BKP_PLAYER_VIDEO" , "BKP_PLAYER_WEBSITE"};

String MJST_TableList[] = {"MJST_PLAYER_BOTS", "MJST_PLAYER_LOG", "MJST_PLAYER_LOGIC"
, "MJST_PLAYER_LOGIC_CALL", "MJST_PLAYER_MC_EVENTS", "MJST_PLAYER_MC_LINKS"
, "MJST_PLAYER_MC_NEWS" , "MJST_PLAYER_MUSIC", "MJST_PLAYER_NOTIFY_QUEUE"
, "MJST_PLAYER_OBJECTS" , "MJST_PLAYER_RECORDS_TO_PROCESS", "MJST_PLAYER_RECORD_OF_LOGIC_EV"
, "MJST_PLAYER_SEARCH" , "MJST_PLAYER_SOUND", "MJST_PLAYER_UMS_QUEUE"
, "MJST_PLAYER_VIDEO" , "MJST_PLAYER_WEBSITE"};

try {
Properties dbprops = new Properties();
con = DriverManager.getConnection(sDBConnPool, null);
statement = con.createStatement();

for ( int i = 0; i < BKP_TableList.length; i++ ) {

String dbQuery = "SELECT * FROM MJST_TableList["+i+"]";

System.out.println(dbQuery);

resultset = statement.executeQuery(dbQuery);

// rsmd = resultset.getMetaData();
// j = rsmd.getColumnCount();

// sb.append("INSERT INTO MJST_TableList[i] (");

/*
for (int k=0; k<j ; k++) {>
String column = rsmd.getColumnName(k);
sb.append(column);
if ( k < j ) {
sb.append(",");
} else {
sb.append(")");
}
}

resultset = statement.executeQuery("SELECT * FROM BKP_TableList[i]");
rsmd = resultset.getMetaData();
j = rsmd.getColumnCount();

sb.append("SELECT ");

// Don't append the last column name i.e PLAYER_LEVEL
for (int k=0; k < j-1 ; k++) {
String column = rsmd.getColumnName(k);
sb.append(column);
if ( k < j-1 ) {
sb.append(",");
}

}

sb.append("FROM BKP_tableList{i]");
sb.append("WHERE BKP_TableList[i].PLAYER_ID = MJST_TableList[i].PLAYER_ID AND BKP_TableList[i].PLAYER_LEVEL='"+playerLevel+"'");
sb.append("AND PLAYER_ID='"+normalizePlayerID(playerId)+"'");

*/

}

// int insertCount = statement.executeUpdate(sb.toString());
Tue May 01 16:09:38 GMT+00:00 2001: <ServletContext-Servlets> weblogic.servlet.JSPServlet: Generated java file: /opt/devpkg/weblogic_instances/wl9000/classfiles/jsp/_SetPlayerState.java
SELECT * FROM MJST_TableList[0]
ORA-00933: SQL command not properly ended

Hi Peter,
Okay , I got what you wanted to say.
Let me try to get the table name there.
thanks
Ravi