aspose file tools*
The moose likes JDBC and the fly likes HOW DO I COPY THE DATA FROM ONE SET OF TABLES TO ANOTHER Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "HOW DO I COPY THE DATA FROM ONE SET OF TABLES TO ANOTHER" Watch "HOW DO I COPY THE DATA FROM ONE SET OF TABLES TO ANOTHER" New topic
Author

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

ravi janap
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
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


SCJP, SCJD, SCWCD, SCBCD, SCEA
bill bozeman
Ranch Hand

Joined: Jun 30, 2000
Posts: 1070
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
ravi janap
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
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

Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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
ravi janap
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
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

ravi janap
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
Hi Peter,
Okay , I got what you wanted to say.
Let me try to get the table name there.
thanks
Ravi
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: HOW DO I COPY THE DATA FROM ONE SET OF TABLES TO ANOTHER