How to use an SQL statement to access database objects in different database servers

In our databaseProgramThis is often the case during development: You need to import data from one database server to another database server table. We usually use this method: First extract the data from a database and put it out, and then insert the data into the target database. This method is less efficient, it is also cumbersome to write programs and error-prone. Another method is to use the BCP or bulk insert statement to import data to a file and then export the data to the target database from this file. Although this method is more efficient, however, there are also a lot of disappointments. It is very troublesome to find the data import file on another machine during the import.
The most convenient method is the most efficient method. It should be like this:
For example, there are two database servers: ZL and ljw, both of which have a database taxitemp (or different names). The database has a table named users, now we want to import the users data in ZL to ljw. We can write SQL statements like this (assuming the ZL database is connected now ):
Insert into ljw. taxitemp. DBO. Users
Select * from users
In this way, data replication between different database servers is completed through an SQL statement.
Some people may say that I can write such SQL statements, and I also thought about it, but it cannot be executed.
Indeed, such a statement alone cannot be executed, because the database does not know what server ljw is or how to log on. Of course, an error will be reported.
We can solve the problem of registering a remote database server and logging in as follows:
Register a remote database server:
Exec sp_addmediaserver 'ljw ', n' SQL Server'
How to register a remote database server:
Exec sp_add1_srvlogin 'ljw ', 'false', null, 'sa', 'zz'
As for the detailed usage of these two stored procedures, I will not talk about them any more. Let's take a look at the help to understand.

You only need to register the remote database server and the logon method first, and then you can use the remote database as a local database.