Copy Table in a MySQL Database
In this section, you will learn to
copy one table to another in a same MySQL
database.
Description
of Code:
This program helps you in copying one
table to another in a same MySQL database.
To copy any table, firstly, you need to establish the connection with MySQL
database (jdbc4). This database has both
the tables (Copyemployee and employee table). That is the one which is to be copied to the other
table and the second is the table to be copied. For copying a table, we have applied the “INSERT INTO
Copyemployee SELECT * FROM employee” SQL statement. Whenever a
table is copied, it displays “Numbers
of row(s) affected”. If none of the rows is copied then it shows a message
“Don’t
add any row!”.
[Note: Both tables have the same
field and its data type.]
Syntax
for copy tables:
INSERT INTO <new_table_name>
SELECT * FROM <old_table_name>
Here is code of program:
import java.sql.*;
public class CopyOneTableToAnother{
public static void main(String[] args) {
System.out.println("Copy data from one table
to another in a database!");
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "jdbc4";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "root";
try {
//Load jdbc driver
Class.forName(driver).newInstance();
//Establish the connection
conn = DriverManager.getConnection(url+dbName,userName,password);
Statement st = conn.createStatement();
//Copy table
int rows = st.executeUpdate("INSERT INTO Copyemployee
SELECT * FROM employee");
if (rows == 0){
System.out.println("Don't add any row!");
}
else{
System.out.println(rows + " row(s)affected.");
conn.close();
}
}
catch (Exception e) {
e.printStackTrace();
}
}
}
|
Download this program.
Table Name: employee
| empId |
empName |
empSal |
| 1 |
Vinod |
50000 |
| 2 |
Sushil |
80000 |
Table Name: Copyemployee
| empId |
empName |
empSal |
| 8 |
AmarDeep |
10000 |
| 7 |
Noor |
50000 |
Output of this program:
C:\vinod>javac CopyOneTableToAnother.java
C:\vinod>
C:\vinod>java CopyOneTableToAnother
Copy data from one table to another in a database!
2 row(s)affected.
C:\vinod> |
After copy table: Copyemployee
| empId |
empName |
empSal |
| 8 |
AmarDeep |
10000 |
| 7 |
Noor |
50000 |
| 1 |
Vinod |
50000 |
| 2 |
Sushil |
80000 |
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
