Ndtyjky

I am trying to run the below query and insert the output of it into another table (postgres db):

    String ssnQuery = "SELECT period_year, period_name, period_num, NULL as count_of_issues,

    ledger_id,

    balancing_segment,

    Count(*) AS count_of_account_segments,

    Sum(accounted_period_net_dr) AS balance_accounted_period_net_dr,

    Sum(accounted_period_net_cr) AS balance_accounted_period_net_cr,

    Round(Sum(accounted_period_net_dr_cr)) AS balance_accounted_period_net_dr_cr_diff,

    Sum(entered_period_net_dr) AS balance_entered_period_net_dr,

    Sum(entered_period_net_cr) AS balance_entered_period_net_cr,

    Round(Sum(entered_period_net_dr_cr)) AS balance_entered_period_net_dr_cr_diff,

    Sum(begin_balance_dr) AS begin_balance_dr,

    Sum(begin_balance_cr) AS begin_balance_cr,

    Round(Sum(net_beginning_balance)) AS net_beginning_balance,

    Round(Sum(net_closing_balance)) AS net_closing_balance

    FROM schema.tablename";



try {

    pstmnt       = financeConnection.prepareStatement(ssnQuery);

    rs           = pstmnt.executeQuery();

    rsmd         = rs.getMetaData();

    for(int i=1; i<=rsmd.getColumnCount(); i++) {

        columnNames.add(rsmd.getColumnName(i));

        if(i == 1) {

            queryColumns = rsmd.getColumnName(i);

        } else if(i<7) {

            queryColumns += ", " + rsmd.getColumnName(i);

        } else {

            queryColumns += ", value_" + (i-7);

        }

    }

    while (rs.next()) {

        queryValues = " ";

        for(String colname: columnNames) {

            if(queryValues.isEmpty()) {

                queryValues = rs.getString(colname);

            } else {

                queryValues += rs.getString(colname) + ", ";

            }

        }

        remCommas = queryValues.replaceAll(", $", "");

        insertQuery = "INSERT INTO bdmerge.gen_audit_func_hive_results (run_id, run_date, run_date_ist" + queryColumns + ") VALUES (" + runid +"," + utcTimeStamp + "," + istTimeStamp + "," + remCommas + ")";

        System.out.println("Final Insert query: " + insertQuery);

    }

} catch (SQLException e) {

    e.printStackTrace();

} catch(Exception e) {

    e.printStackTrace();

}

To insert the output of above query, I formed the insert query according the column names in the destination table as below:

INSERT INTO schema.destinationTable (run_id, run_date, run_date_ist, source_system_type, source_system, module, source_table_name, period_year, period_name, period_num, count_of_issues, count_of_accounted_issues, count_of_entered_issues, value_0, value_1, value_2, value_3, value_4, value_5, value_6, value_7, value_8, value_9, value_10, value_11, value_12) VALUES (781,2018-11-12 08:15:32.0,2018-11-12 13:45:32.0, 2018, OCT-18, 10, null, 1, 1, 2073, ATRS, 6135, 6.2778220466107E11, 6.277946274560101E11, -1.2422795E7, 5.929031383587201E11, 5.9291556115366E11, -1.2422795E7, 3.931397937759116E13, 3.9313979377591164E13, 0.0)

But the destination table's columns:

run_id, count_of_issues, count_of_accounted_issues, count_of_entered_issues

are in numeric format (working on postgres db) and all others are varchar(1000).

In order to insert the varchar data, I need to enclose the column values from value_0 till value_12 in double quotes.

Without properly modifying the data, I am getting SQLException while inserting which is expected.

Is there any way I can just enclose those varchar column values from the resultSet in double quotes and insert them into destination table?

You need to add double quotes to the string values; Use Escape characters, like this:

String quotedStr = "Non Quoted," + " "Quoted".";

Modifying your code to address the problem:

   while (rs.next()) {

        queryValues = " ";

        int i = 0; 

        for(String colname: columnNames) {



            if(queryValues.isEmpty()) {

                queryValues = rs.getString(colname);

            } else {

            if (i >= 7)

                    queryValues += """ + rs.getString(colname) +""" + ", ";

            }

            else

                queryValues += rs.getString(colname) + ", ";

            i++;

        }