0

I am trying to figure out what would be the best way to copy SQL database views from one database to another.

Both databases are hosted on two different servers, so will require firewall access between them. I will need to send the views of the data on a daily basis to the required database.

Copy table to a different database on a different SQL Server - Tried this example but not getting any luck.

cardiokhan
  • 29
  • 6
  • 8
    What does `not getting any luck` mean? Is there an error message? – csabinho Nov 04 '19 at 22:14
  • Sorry the errors I do not have at hand but I am just simply trying the following: 1)Loading data from views to views 2)How to schedule it to run on a daily basis 3)Do I require a symmetrical key and how this can be developed/found? – cardiokhan Nov 04 '19 at 22:40
  • On your first point there, you cannot load data into a view. It would have to be from view to a table at least. – FernandoG Nov 04 '19 at 23:17
  • You should do a web search for "database synchronization". As is, your question is quite broad. – Robert Nov 05 '19 at 03:01
  • If you don't know the errors, it appears that you actually haven't undertaken any troubleshooting of the errors? – Nick.Mc Nov 05 '19 at 05:13

1 Answers1

0

It looks like the example you linked suggests using linked servers. That in my opinion is the best route when going server to server. But since you mentioned that you are having trouble with that you can try using an OpenRowSet.

I won't bother to add my own examples here because others like this one are already great examples.

Of course you would still need to make sure you can connect to that server within the machine you are using. I am not sure how your set up is or what it would take.

In any case, whenever you decide what is best, use SQLAgent to schedule a daily job. This daily job can execute queries or stored procedures. So, you could create a stored procedure that declares an OpenRowSet and does whatever you need.

FernandoG
  • 460
  • 5
  • 16