I have to sync two databases. Database 1 has data I would like to use to sync everything. For example,
select * from administration.operations where asset_no = '555' --Suppose this query returns an asset with five rows of data in the "operations" table.
Database 2 can only be synced by using a dml statement. Which I've been creating by running a query like the one above on Dbase 1, then CTRL+A > Right Click > Export. This opens the export wizard, wherein I enter the appropriate table name "operations" in the 'Table Name' field (it default-fills to "EXPORT TABLE", which would not work). Because of primary keys and parent/child relationships, I generally have to run this process on 5-10 tables in order to ensure the second database is getting all the data related to asset_no=555. After I've run the query on all the relevant tables and copy/pasted the exported 'insert' statements in a dml, I can send it to the other server and now asset number 555 looks the same on database 2 as it looks on database 1.
I would really like to automate this process, since I do this anywhere from 10-30 asset_no's a day- that's 50-300 times a day I'm having to select * > Export Wizard > copy/paste into dml. I would love to automate this process. Is it possible to create a sql script to pull the select * results and automatically create the export commands without the user (me) having to click around so much?
I've tried writing scripts to generate the INSERT statements but some of the tables I have to sync contain 70+ columns- it's just not feasible, so I'd like something dynamic. With that in mind, I've never heard of a sql script that can do the same as the Export Wizard. So here we are. I also cannot use python or any other language to register mouse clicks or anything like that- the whole thing would have to be a sql statement that runs within Oracle.