I'm facing a challenge and I was hoping to get some advices from this community.
I have 2 SQL Server databases on 2 different servers. One with a LOT of data and one that will be used for reporting. The DB used for reporting does not need every tables and column, only a certain subset of data (like 10%). I also need to transform the data since in the big database, the data can be nested in XML and we want to extract this into clean columns in the reporting DB.
For internal reasons, my team want to avoid using an SSIS package for that. So, to accomplish this goal, I need to use SQL statements that will be executed through Java code.
I looked at the MERGE statement that seems to do what I need, but I don't get how I can use this between two databases on different servers. Is that even possible ?
Is there any libraries / frameworks that could do that kind of operations (insert/update/delete) between 2 databases ?
Any help or advices would be greatly appreciated.