0

I have a JAVA RFC program that getting data from SAP table and insert those data into a SQL table. Every time JAVA program run data will insert into SQL table. So there may be duplicate data come. My problem is how to omit those duplicate data from insertion. I couldn't figure out a right query. I am not using two tables to compare. I have only one table and new data coming from SAP side and i use a loop to insert data. I don't want to delete already exist data.

SQL table structure...

ITPITP   ITPNAM             ITPSCT    ITPACTYN
2000348  175/70 R13 CT      FG0009    Y
2000355  2ND-175/70 R 13    FG0009    Y
2000364  2ND-185/70 R 14    FG0009    Y
2000365  3RD-185/70 R 14    FG0009    Y
2000370  2ND-185/70 R 14    FG0009    Y

Primary key is ITPITP

this is how I insert values.

    StringBuilder sbQuery = new StringBuilder(
    "INSERT INTO ITPMAST(ITPITP,ITPNAM,ITPSCT,ITPACTYN) NOT IN 
(SELECT ITPITP FROM ITPMAST) VALUES");

                // --- create query ---------------------------------
                for (int i = 0; i < table.getNumRows(); i++) {
                    // table.setRow(i);
                    sbQuery.append("(?,?,?,?),");
                }

                sbQuery.deleteCharAt(sbQuery.length() - 1);
                sbQuery.append(";");
                qex.setUpdateQuery(sbQuery.toString());// *****************
                elements.clear();

                String strPattern = "^0+(?!$)";

                for (int i = 0; i < table.getNumRows(); i++) {
                    table.setRow(i);

                    elements.add(table.getString("MATNR").replaceAll(strPattern, ""));
                    elements.add(table.getString("MAKTX"));
                    elements.add(table.getString("MATKL"));
                    elements.add("Y");

                }
D.Madu
  • 507
  • 2
  • 8
  • 28
  • Just modify your `INSERT` query to use a `WHERE NOT EXISTS` clause. If you get stuck and can't figure out the duplicate link, then drop a comment here and the question can be reopened. – Tim Biegeleisen Jan 17 '18 at 11:14
  • I couldn't figure out a way. cause there they compare using two table. I am only using one table and new data coming from SAP – D.Madu Jan 17 '18 at 11:24
  • OK fair enough. You could just bite the bullet, insert the duplicates, and then remove them afterwards. – Tim Biegeleisen Jan 17 '18 at 11:29
  • I cannot insert duplicates cause *ITPITP* is the primary key and sql not let me and i don't want to modify existing data in the table. – D.Madu Jan 17 '18 at 11:39
  • Then insert into a temporary table, scrub it there, and insert from the temp table into your actual target. – Tim Biegeleisen Jan 17 '18 at 11:40
  • You might want to try to prevent the insert by a trigger on the destination table. Another possibility might be to handle the insert within a stored procedure which checks the existing data fitrst. – Tyron78 Jan 17 '18 at 12:32

0 Answers0