• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 389
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1070
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 389
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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

 
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 389
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 389
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Peter,
Okay , I got what you wanted to say.
Let me try to get the table name there.
thanks
Ravi
 
reply
    Bookmark Topic Watch Topic
  • New Topic