Recently I needed to read data from a file and merge/upsert it with existing data in an Oracle table. Until Boomi get to implement this "Dynamic Database Upsert" idea, few solution options came to mind.
- Option 1: Create an Oracle external table that points to the file, then use the System Command shape to invoke the Oracle SQL merge statement that merge the data into the target table.
Pros: Simple and works great if the file is already on the database server.
Cons: The file should be accessible on the database server. If the file is on a cloud atom, then an extra step of FTPing the file to the database server is needed. We also need to create the external table, which is an extra database component just for the purpose of this process.
- Option 2: Use Boomi to read the flat file and insert the data into a temp table, then use the Oracle merge statement to merge the content of the temp table with the target table.
Pros: Unlike option 1, Oracle does not need to have direct access to the file.
Cons: Need to create an extra intermediate temp tables to hold the data.
- Option 3: Read the flat file and directly merge the data with the target table. At first I didn't know how to do this, then I came across this question, and thanks to @Thorsten Herrmann for answering it. Basically you can access the result of a mapping directly into an Oracle connector database-profile by setting the profile type to "Standard Insert/ Update/ Delete", then have a sql statement as follows: "select ? as field1, ? as field2, ? as field3, ... from dual". Of course table "dual" is also a feature that is unique to Oracle. You can simulate the "dual" table in other databases by creating a table that has one dummy row and name it dual.
Pros: No need to create any extra database tables. No need to have the file accessible by Oracle. If Boomi can see the file, this solution will work.
Cons: The file need to be where Boomi can see it.
For my purposes, Option 3 worked best.
But Wait ...
However, I had a dozen files to merge with a dozen tables. Writing the merge sql became quite tedious and error prone specially for those files and tables that has many many columns. After doing a couple of those I thought enough is enough. Time to have the computer do the tedious work for me.
Please note that I am assuming the source and target column names are the same.
Hopefully this will help with the grunt work of writing a merge sql specially for profiles with many columns.