Execute SQL commands between multiple SQL Server servers

In project development, we encountered the following problem: data needs to be imported from another system to the new system. the SQL server services of the two systems are located on different SQL Server servers.
I tried to write a stored procedure in the new system to implement this function, I wrote the following T-SQL command to test whether this idea is feasible (the connection to the SQL Server server [test] of another system has been established in the enterprise manager on the SQL Server server of the new system):

1 Select   *   From   [ Test ] . [ Mydata ] . DBO. [ User ]
2 -- Where:
3 -- [Test] is the name of the database server of another system.
4 -- [Mydata] indicates the database name on that server.
5 -- [User] is the table name.

After the command is executed, the system reports "the server 'test' cannot be found in sysservers '. Run sp_addmediaserver to add the server to sysservers ." In this case, it seems that it is feasible to execute SQL commands on multiple SQL Server servers, but you need to set them on the SQL server that executes SQL commands, so I quickly checked the information about the storage process of the sp_addmediaserver system. After repeated tests, after running the following SQL command on the SQL Server server of the new system, finally, I can operate the data on the [test] server on the SQL server of the new system.

1 Exec Sp_addmediaserver
2 @ Server   =   ' Test ' ,
3 @ Provider   =   ' Msdasql ' ,
4 @ Provstr   =   ' Driver = {SQL Server}; server = test; uid = sa; Pwd =; '
5 -- @ Server is the name of the server referenced when you execute SQL commands in the future. It can be named at will, not necessarily the same name as the real SQL Server server.
6 -- @ Provider: Specify the parameters in the data table.
7 -- @ Provstr database connection word, no need to say more.

Note: For SQL Server, you can use"Sqloledb"As @ provider, but due to my level limit, I failed to try it out, so I had to use the upper limit method to implement it. If anyone knows how to implement it, please let me know, I thanked you first.