DB2 Single-Sheet merge statement

<span style= "font-family:arial, Helvetica, Sans-serif;" >merge into channel_agent_people_number_201503 a using (SELECT? time_id, huh? City_name, huh? Node_name, huh? Node_code, huh? People_number, huh? Vehicle_running_costs, huh? Mail_transport_fees, huh? Training_costs, huh? Consumables, huh? Other_office_expenses from SYSIBM. SYSDUMMY1) B on a.time_id = b.time_id and a.city_name= b.city_name and a.node_name = b.node_name and A.node_code = B.nod  E_code when matched then update set a.people_number = B.people_number, a.vehicle_running_costs =b.vehicle_running_costs, a.mail_transport_fees= b.mail_transport_fees, a.training_costs = b.training_costs, A.CONSUMABLES = B.CONSUMABLES, A. Other_office_expenses = b.other_office_expenses When isn't matched then INSERT VALUES (B.time_id,b.city_name, B.node_name , B.node_code,b.people_number,b.vehicle_running_costs, B.mail_transport_fees,b.training_costs,b.consumables,b. other_office_expenses) </span>

Today in the development of a single table to do the merge statement, using the Spring jdbctemplate batch modification, so to pass the parameters in, using the above merge statement to complete, here to make a record.

Some Java code is as follows:

private void Uploadagentchannel (Workbook Workbook, String City, int month) {Sheet Sheet = workbook.getsheetat (0); list<object[]> params = new arraylist<object[]> (); for (int i = 3; I < Sheet.getlastrownum (); i++) {PARAMS.A DD (new object[] {month,sheet.getrow (i). Getcell (0). Getstringcellvalue (), Sheet.getrow (i). Getcell (1). Getstringcellvalue (), Sheet.getrow (i). Getcell (2). Getstringcellvalue (), Sheet.getrow (i). Getcell (3). Getnumericcellvalue (), Sheet.getrow (i). Getcell (4). Getnumericcellvalue (), Sheet.getrow (i). Getcell (5). Getnumericcellvalue (), Sheet.getrow (i). Getcell (6). Getnumericcellvalue (), Sheet.getrow (i). Getcell (7). Getnumericcellvalue (), Sheet.getrow (i). Getcell (8). Getnumericcellvalue ()}); To determine if the corresponding month table exists, create the table if it is not present (!istableexists ("Channel_agent_people_number", Month)) {Jdbctemplate.execute ("Create Table Channel_agent_people_number_ "+ month +" like Channel_agent_people_number ");} Use the merge statement to modify and insert the corresponding data string sql = "Merge into Channel_agent_people_number_" + month+ "a using (select"+ " ? time_id, huh? City_name, huh? Node_name, huh? Node_code, huh? People_number, "+"? Vehicle_running_costs, huh? Mail_transport_fees, huh? Training_costs, "+"? Consumables, huh? Other_office_expenses from SYSIBM.  SYSDUMMY1) B "+" on a.time_id = b.time_id and A.city_name= b.city_name and "+" A.node_name = B.node_name and A.node_code = B.node_code "+" when matched then update set a.people_number = B.people_number, "+" a.vehicle_running_costs =b.vehicl E_running_costs, "+" a.mail_transport_fees= b.mail_transport_fees, "+" a.training_costs = b.training_costs, A. Consumables = B.consumables, "+" a.other_office_expenses = b.other_office_expenses "+" when not matched then INSERT VAL UES (B.time_id,b.city_name, "+" b.node_name,b.node_code,b.people_number,b.vehicle_running_costs, "+" B.MAIL_ Transport_fees, "+" b.training_costs,b.consumables,b.other_office_expenses) "; jdbctemplate.batchupdate (SQL, params );}

DB2 Single-Sheet merge statement