Linked server #Back to Basics
October 30, 2015
Really back to basic, but here is another example how to move data from one server to the other in a scheduled job.
First make sure, when you use an sql account to connect both servers, the account exist on both ends of the line.
Now we create a linked server, after all my former tutorials, I don't have to go into detail, either script it or use SSMS gui, un this case I'm using it for a MSSQL to MSSQL this is easy peasy.
Create a database and table if it doesn't exsist on the server you want to insert the data in.
If all is setup, write a simple INSERT INTO script:
INSERT INTO [FunFactory_II].[dbo].[Accounts_NAWT]
And the last step is to create a job, to start the import automatically and schedule it to run on a given time.
This is my really #back to basic linked server tutorial, but you can add and tweak functions into the script and built fully automated solutions for your data.