0

I need to insert new rows into an oracle(12c) SQL table only if they don't exists using java code It is possible that there will be more then 100 rows to check and insert

ideally I would like to have one merge statement with multiple inserts something like this:

MERGE INTO some_table t
USING(???)
ON(???)
WHEN MATCHED THEN UPDATE ???
WHEN NOT MATCHED THEN INSERT (t.id, t.val)
     value("some_id","some_data")
      ...
      ...
      ...

but I have trouble with syntax and i can't find any examples for this case ether

I would like to know:

  1. if this even possible
  2. if yes, is it the correct way to go or there is a better solution
  3. what should be the syntax(some example will be great)
  4. if it not possible than what is the correct way.

thanks for help

Panteleev Dima
  • 175
  • 2
  • 18
  • java's irrelevant. you're writing an sql query, which would be the same query no matter what language you're using. – Marc B Apr 28 '16 at 20:40
  • It is possible, and often it is the best solution even for insert (and especially so if you need to insert AND to update rows in the target table). The syntax can be found, for example, here: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm Will look for a couple of examples for you. –  Apr 28 '16 at 21:04
  • thanks mathguy, I checked that link already, but it shows only how to merge rows from other table and I need to add new rows - this is what confuses me, thanks again – Panteleev Dima Apr 28 '16 at 21:09
  • No, you don't merge only from tables, you can also merge from views and subqueries. How would you insert otherwise? One row at a time, or do you have a query of some sort that returns what needs to be inserted? If it's one row at a time (such as, typing literals at the keyboard), then the insert statement is the right solution. If you are inserting rows created somehow in the database, then merge may work better. What is the source of the rows you need to insert? –  Apr 28 '16 at 21:16
  • the source of rows is outside of the database it comes from java code: user uploads xml files, application possessing and transforming the data in to new rows – Panteleev Dima Apr 28 '16 at 21:24
  • Write the data to a temp table from your java app. – OldProgrammer Apr 28 '16 at 22:33
  • ok, so basically I use insert to write data into a temp table and then use merge between target table and temp table, am i correct? – Panteleev Dima Apr 28 '16 at 22:37

1 Answers1

0

Following OldProgrammer advice i will create temp table, insert there the new data and the will merge between the two tables

Panteleev Dima
  • 175
  • 2
  • 18