A typical replication solution involves using two commands in the same transaction, one to update existing rows in the remote table and one to add non-existent rows to the remote table:
-- código #1
BEGIN TRANSACTION;
-- atualiza informações existentes no servidor remoto (R)
UPDATE R
set username= L.username,
password= L.password
from Segundo_Servidor.banco.esquema.Teste as R
inner join Teste as L on R.id = L.id
where L.username <> R.username
or L.password <> R.password;
-- acrescenta informações inexistentes no servidor remoto (R)
INSERT into Segundo_Servidor.banco.esquema.Teste (id, username, password)
SELECT L.id, L.username, L.password
from Teste as L
where not exists (SELECT *
from Segundo_Servidor.banco.esquema.Teste as R
where R.id = L.id);
COMMIT;
The above code runs on the local server (L), that is, the first server. That is, it pushes the data to the remote (R) server.
Another solution is possible, with replication code running on each remote server, pulling the data:
-- código #2
BEGIN TRANSACTION;
-- atualiza informações existentes no servidor local
UPDATE L
set username= PS.username,
password= PS.password
from Primeiro_Servidor.banco.esquema.Teste as PS
inner join Teste as L on PS.id = L.id
where L.username <> PS.username
or L.password <> PS.password;
-- acrescenta informações inexistentes no servidor local
INSERT into Teste (id, username, password)
SELECT PS.id, PS.username, PS.password
from Primeiro_Servidor.banco.esquema.Teste as PS
where not exists (SELECT *
from Teste s L
where PS.id = L.id);
COMMIT;
The advantage of code # 2 is that it can be transformed into stored procedure on the main server and executed remotely with EXECUTE AT from the main server. That is, a single version of the code, maintained on the primary server, but run on each remote server from the primary server:
-- código #3
EXECUTE (...) AT Segundo_Servidor;
EXECUTE (...) AT Terceiro_Servidor;
However, either by pushing or pulling the data, this type of replication generates excessive network traffic because to know whether or not the line exists on the practice the contents of the table (remote or local, depending on whether pushing or pulling ) travels across the network.
There are ways to optimize the above codes. But the suggestion is to evaluate the implementation of some kind of automatic replication provided by SQL Server.
PS: I have not tested the solutions proposed above; I hope they do not contain an error.
Youcanevenapplythedifferencesbymatchingthedataorgeneratingascriptthatwilldothis.