How to retrieve a million rows from a table and insert it into another table?

Question

I have a sql join query that returns a million rows.

And that SQL QUERY IS:

 SELECT d.* 
 FROM snomed_conceptdata c, snomed_descriptiondata d 
 WHERE c.active = 1 and conceptid = c.id AND d.active = 1

how do i retrieve this data and insert it into another table without encountering out of memory heap space error.Iam currently using Mysql database.

My current code:

package Snomed.Snomed;

import java.sql.PreparedStatement;
import java.sql.ResultSet;


import catalog.Root;

public class Snomedinfo {

    public void snomedinfoinsert()
    {
    Root oRoot = null;
    ResultSet oRsSelect = null;
    PreparedStatement oPrStmt = null;
    PreparedStatement oPrStmt2 = null;
    PreparedStatement oPrStmtSelect = null;
    String strSql = null;


    ResultSet oRs = null;
    String refid = null;
    String id = null;
    String effectivetime = null;
    String active = null;
    String moduleid = null;
    String conceptid = null;
    String languagecode = null;
    String typeid = null;
    String term = null;
    String caseSignificanceid = null;

    try{
    oRoot = Root.createDbConnection(null);
    strSql = "SELECT d.* FROM snomed_conceptdata c, snomed_descriptiondata d WHERE c.active = 1 and conceptid = c.id AND d.active = 1  "; //this query returns a million rows.


    oPrStmt2 = oRoot.con.prepareStatement(strSql);

    oRs = oPrStmt2.executeQuery();

    String sql = "INSERT INTO snomedinfo_date (refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid) VALUES( ?, ?, ?,?,?,?,?,?,?,?)";
    oPrStmt = oRoot.con.prepareStatement(sql);





        while (oRs.next())
        {
            refid = Root.TrimString(oRs.getString("refid")); 
            id = Root.TrimString(oRs.getString("id"));
            effectivetime = Root.TrimString(oRs.getString("effectivetime"));
            active = Root.TrimString(oRs.getString("active"));
            moduleid = Root.TrimString(oRs.getString("moduleid"));
            conceptid = Root.TrimString(oRs.getString("conceptid"));
            languagecode = Root.TrimString(oRs.getString("languagecode"));
            typeid = Root.TrimString(oRs.getString("typeid"));
            term = Root.TrimString(oRs.getString("term"));
            caseSignificanceid = Root.TrimString(oRs.getString("caseSignificanceid"));  


            oPrStmt.setString(1, refid);
            oPrStmt.setString(2, id);
            oPrStmt.setString(3, effectivetime);
            oPrStmt.setString(4, active);
            oPrStmt.setString(5, moduleid);
            oPrStmt.setString(6, conceptid);
            oPrStmt.setString(7, languagecode);
            oPrStmt.setString(8, typeid );
            oPrStmt.setString(9, term);
            oPrStmt.setString(10, caseSignificanceid);
             oPrStmt.executeUpdate();
        }






    }

    catch (Exception e) {
        e.printStackTrace();

    }

    finally {

        oRsSelect = Root.EcwCloseResultSet(oRsSelect);
        oRs = Root.EcwCloseResultSet(oRs);
        oPrStmt = Root.EcwClosePreparedStatement(oPrStmt);
        oPrStmt = Root.EcwClosePreparedStatement(oPrStmt2);
        oPrStmt = Root.EcwClosePreparedStatement(oPrStmtSelect);
        oRoot = Root.closeDbConnection(null, oRoot);
    }


}
    public static void main(String args[] ) throws Exception 
      { 

      Snomedinfo a = new Snomedinfo();
      a .snomedinfoinsert();

      }


}

I'd probably first try to do the entire thing on the database and if that doesn't work I'd try to use offset and limit to load batches. There's probably also some sort of scrollable result that fetches rows as you iterate over it - I can't have a look right now so I'll leave that for you :) — Thomas, Aug 20 '18 at 16:29
ok but how do i retrieve such huge data ??i tried using limit and offset but as the limit increases the data retrival process gets really slow(takes forever) — , Aug 20 '18 at 16:32
This is a case where ORM solutions can become non-performant. Within the same database, use the answer. If, for some reason, that can't be done, use JDBC. ResultSet does not read the entire data set into memory, so you can read and write. Insert operations can also be done in blocks. — Steve11235, Aug 20 '18 at 16:39
Please don't create a duplicate account, you already repeatedly asked this question under a different account. Why do you persist in asking the same question over and over again? — Mark Rotteveel, Aug 21 '18 at 07:19

ScaisEdge · Answer 1 · 2018-08-20T17:44:28.333

6

In mysql you can use a create select or if the table already exist an insert select

create my_table_copy
SELECT d.* 
FROM snomed_conceptdata c
INNER JOIN snomed_descriptiondata d ON c.active = 1 
          and conceptid = c.id 
              AND d.active = 1

or

insert into   your_table  (col1, col2, col3)
SELECT d.col1, d.col2, d.col3 
FROM snomed_conceptdata c
INNER JOIN snomed_descriptiondata d ON c.active = 1 
          and conceptid = c.id 
              AND d.active = 1.51

be sure you have enough disk space

edited Aug 20 '18 at 17:44

answered Aug 20 '18 at 16:31

ScaisEdge

131,976
10
91
107

@Andreas .. thank you .. for the suggestion answer updated .. – ScaisEdge Aug 20 '18 at 17:45

How to retrieve a million rows from a table and insert it into another table?

1 Answers1