Hey everyone! I often find myself needing to transfer data between two different databases, each accessed through its own JDBC connection. I usually just maintain two connections, retrieve a ResultSet from the first, and manually write the data into the second database. But I'm wondering—are there more efficient or principled methods to handle this? Are there any frameworks, like JOOQ, that simplify this process? I'd love to hear your recommendations! Thanks!
5 Answers
One option is to look into whether the databases you're using can connect to each other directly, allowing you to run SQL queries on remote tables. This could streamline the process if it's a recurring task.
You might want to set up a remote connection between the two databases. This way, you could run a command like INSERT INTO LOCALTABLE FROM REMOTEVIEW to transfer the data directly.
You could consider using two data sources, where you run the query on the first data source, process the results, and then save them using the second data source.
What’s your end goal here? Are you looking to sync data between these databases? Is the plan for one database to eventually take over the other? Understanding your use case would really help in offering specific advice.
From my initial post, it seems like I'll be transferring data from table T1 in DB1 to table T2 in DB2 once a day with some minor changes. I'm looking for a way to do this directly from DB1 to DB2 without needing to load the data onto an ETL machine.
For the best performance, utilize the fastest methods available for each database, like using COPY for PostgreSQL or BULK INSERT for SQL Server. You can also execute these processes in parallel. If you're dealing with PostgreSQL, a good strategy is to create an UNLOGGED temporary table, load the data quickly with COPY, set up any necessary indexes afterward, and then swap it with the main table or perform an upsert.
That's not feasible in my case since the databases are from different vendors.