CODEWITHSUNDEEP
javaoracle-databasejdbc
Johnbsdk10
Johnbsdk10

Reputation: 89

Copying the data from one table to other table in oracle database using Java

I am new to Java and

I have a table name TABLE1 having column names NAME, ROLL.

CREATE TABLE TABLE1(NAME VARCHAR2(4), ROLL NUMBER(3));

INSERT INTO TABLE1 VALUES ('SAMY', 101);
INSERT INTO TABLE1 VALUES ('TAMY', 102);
INSERT INTO TABLE1 VALUES ('JAMY', 103);
INSERT INTO TABLE1 VALUES ('RAMY', 104);

I have an other table name TABLE2 having column names NAME, ROLL.

CREATE TABLE TABLE1(NAME VARCHAR2(4), ROLL NUMBER(3));

We need to write a Java program to migrate the data from TABLE 1 to TABLE2 in oracle and print the result in console.

I am using Oracle SQL developer. I have written a Java program but not sure whether it is accurate or not.

    java.lang.Class;
    java.sql.Connection;
    java.sql.DriverManager;
    java.sql.ResultSet;
    java.sql.Statement;

    public class JDBCDemoConnection
    {
       public static void main(String[] args)
       {
          try
             {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                Connection connection = null;
                connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "username", "password");
                Statement smt = connection.createStatement();
                String sql = "INSERT INTO TABLE2 (NAME,ROLL)" + "SELECT NAME, ROLL FROM TABLE1";
                
                int i = smt.executeUpdate(sql);
                System.out.println(i+ " row is inserted into the table");

                connection.close();

             }
            catch(Exception E)
             {
               System.out.println(E);
             }
       }
    }

Upvotes: 0

Views: 818

Answers (1)

sas
sas

Reputation: 532

The problem is with the SQL query you are using. After concatenation, the query looks like INSERT INTO TABLE2 (NAME,ROLL) SELECT NAME, ROLL FROM TABLE1 which is not a valid SQL statement.

Simply make use of SELECT INTO statement to copy data from one table into a new table.

Statement smt = connection.createStatement();
String sql = "SELECT NAME, ROLL INTO TABLE2 FROM TABLE1";
boolean result = smt.execute(sql);

Upvotes: 1

Related Questions