I have this seemingly really simple problem, but I can't find a decent solution anywhere, been searching for days now...
Please bear in mind I'm not a full-fledged DBA. I could solve it completely in .Net code, but I had hoped there would be a relatively easy solution in Sql directly...
Here goes: I need to transfer (insert and update) data from one database to another. Sounds really simple, right?
But there are some constraints:
- the target database is not on the same network: it is not directly accessible, I can only transfer files.
- the data I want to transfer is not in "fixed" columns. They can change frequently. The target database has tables to receive the data, but the data I'm sending through is not always the full record. I may send 4 out of 10 columns, or 25 out of 30, or whatever. Always subsets of the complete records. Inserts are also possible, in that case the receiving table will allow null values or have default values.
- it should ideally be a stored procedure that creates the file(s) at the sending db, and a sp that reads the file at the receiving db. Can't use anything manual like SSMS wizards, and prefer not to use powershell due to limitations at the receiving end (those computers are not under my control).
Column names and data types are -of course- the same on sending and receiving end.
How can I do this without needing to build a whole bunch of sql commands dynamically? I was looking for an XML solution, but I can't find anything that's dynamic enough to do this. So any solution that lets me realize this, doesn't matter what file format, would work.
I really feel like a complete newbie to be asking this, but I just can't find anything that fits...
Any help is hugely appreciated!