how to extract data from one table and insert into another table

i am writing a code to retrieve two columns from a table and store it in another table.i am using MS Accesses as a database. the code is as follows

public void connectDb(){
   try{

  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  Connection con=DriverManager.getConnection("jdbc:odbc:harish","scott","tiger");
  Statement st= con.createStatement();
  ResultSet rs=st.executeQuery("select sev,al from pgw_alarm where sev='000'");
  while(!rs.next())
  {
   String sev= rs.getString("sev");
   String al= rs.getString("al");
   st.executeUpdate("insert into info_alarm values('"+sev+"','"+al+"')");
  }
}catch(Exception e){e.printStackTrace();
    System.out.println("some error");}
}

the following stack trace

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)

    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
    at sun.jdbc.odbc.JdbcOdbc.SQLGetDataString(JdbcOdbc.java:3907)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getDataString(JdbcOdbcResultSet.java:5698)
    at sun.jdbc.odbc.JdbcOdbcResultSet开发者_运维技巧.getString(JdbcOdbcResultSet.java:354)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:411)

please help me in solving this problem.....

---

This should be possible within a single query:

INSERT INTO info_alarm (sev, al)
  SELECT sev, al FROM pgw_alarm where sev='000';

It will be cleaner and way faster.

The ! in while (!rs.next()) is probably just a typo, right?

Edit: To answer you question, looking into JavaDoc:

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

Basically the ResultSet is still reading from the statement but you you closed it by executing another query on the same Statement. If you want to do it this way you need to use two separate statements.

---

 while(!rs.next())

Why are you checking for !rs.next()? ResultSet.next() returns true if the cursor is pointing at a valid row and false if no more rows exist. If your query is returning no results, rs.next() will return false. !rs.next() -> not false -> true -> your while block will execute on an empty result set.

This is almost certainly what your problem is. Change it to while(rs.next())

---

Pretty sure that the problem is caused by reusing the Statement instance. It says in JavaDoc:

All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

So when you execute the insert statement, the actual result set is closed, and that may throw the exception. ("invalid cursor state" is another hint)

Create to different Statement instances, one for the read, the other one for the multiple inserts. That should fix this issue.