We have a vendor machine on our network which is at a remote location and I would like to copy about 15 tables from that database to our location database so I can build reports using data from our local database and the remote database tables. The machine is not on our domain but is on our network. I have made it possible to connect to the remote database through settings on the remote database and created a user that has admin credentials. I would like to copy the data from these tables on a weekly basis and at the end of the month truncate all the data and start with an empty database for the next month.
What is the best way to accomplish this? I believe I will need to create a Stored Procedure to do this but want to be sure.
Thank you in advance for your help.