How to do mirroring or replication in table level in SQL Server with SQL Query-CodePudding

Inserting one row from one table to another table of second database

 Insert into Task1.dbo.Patients (FirstName, Lastname, Address, ContactNo,Gender,DateOfBirth )
  Select FirstName, Lastname, Address, ContactNo,Gender,DateOfBirth from Tasks.dbo.Patients

I want to insert one copy of only the inserted row in similar table present in another database. Insert Into is a choice but it copies the whole data from source table and append into destination table. I want to mirror only one row at the time of insertion in another database.

CodePudding user response：

You can use a straightforward trigger for this

CREATE TRIGGER tr_Patients_Tasks1Copy ON dbo.Patients AFTER INSERT
AS

SET NOCOUNT ON;

IF EXISTS (SELECT 1 FROM inserted)
    INSERT Task1.dbo.Patients
      (FirstName, Lastname, Address, ContactNo, Gender, DateOfBirth)
    SELECT FirstName, Lastname, Address, ContactNo, Gender, DateOfBirth
    FROM inserted i;

GO

Note that the inserted table may have multiple or even zero rows