I have multiple SQL server across the States that I need to connect to and pass data from one to another. All the databases are the same scheme/Configuration/etc.
What I had done was link all the other SQL instances to the main SQL server and connect to it that way. However, it seems to be very slow.
INSERT INTO [WI-SVR2].DB2.DBO.EMPLOYEE(ID, NAME)
SELECT ID, NAME FROM [NY-SVR1].DB1.DBO.EMPLOYEE
Is it possible to do something with connection string connecting directly to each database and passing data that way?
Dim conWI AS String = "data source=WI-SVR2;persisty security info=false;initial catalog=DB2;UID=sa;PWD=Password1"
Dim conNY AS String = "data source=NY-SVR1;persisty security info=false;initial catalog=DB1;UID=sa;PWD=Password1"
Not sure how to incorporate that so that it would open one database and pass data from one to the other. Any ideas how to do this or can it be done?