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]
(AccountNumber]
,[Name]
,[Street]
,[PostalCode])
SELECT
[AccountNumber]
,[Name]
,[Street]
,[PostalCode]
FROM [FunFactory_I].[dbo].[BSN].[Accounts]
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.
For example a scheduled sql job to insert data from the linked server.
