how to copy database table in java ?

cofactor
cofactor used Ask the Experts™
on
I have a "copy"  button in my jsp page.

When I click on copy button , I want to copy data from  few tables to  other few tables  in the same database schema.

source table name and destination table name are different.
source column name and destination column name are different.

My database mysql 5 , Jboss 5  server , JDK 1.5

Whats the best  approach I can think of  ?


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2011
Awarded 2011

Commented:


When you click a button you can load the servlet which would
through jdbc execute sql command which would take columns
from one table and insert their values to another table using
the sql syntax like below:

insert into table1 (column1, column2, ...) select column1, column2 ... from another_table where ...



Look for some example of such query, say here:
 
http://stackoverflow.com/questions/74162/how-to-do-insert-into-a-table-records-extracted-from-another-table

Commented:
Hi cofactor,
You can create a stored procedure(SP) and then invoke it as a callable statement. Within the SP, you can use SQL similar to what for_yan has suggested.
I have given code snippets for creating SP & invoking it from Java.
Hope this helps!


//assuming you have the connection object setup already..
CallableStatement cs = conn.prepareCall("{ call sp_copydata() }");
// execute the stored procedure
cs.execute();

And you can create the SP like this:

CREATE PROCEDURE sp_copydata()
BEGIN
	insert into table1 (column1, column2, ...) select column1, column2 ... from table2;
END

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial