We have two Databases, Source -> 'HR_TimeOffice' and Dest -> 'HR'.
we have to create a trigger which just replicate new entries automatically from sources DB's table to destination DB's table.
INSERT INTO [HR].[dbo].[EmpAttendance]
SELECT RIGHT(REPLICATE(0,5)+[HR_TimeOffice].DBO.EmpAttendance.EmpCode ,5) EMPLOYEENO,
CONVERT(CHAR(8), i.DateTime, 112),i.DateTime,
case when (i.InOut='I') then 'IN' else 'OUT' End,'0',null
FROM inserted i
INNER JOIN [HR_TimeOffice].dbo.EmpAttendance ON
EmpAttendance.EmpCode = i.EmpCode
the trigger did its job as it should, but it insert multiple entries with same data in destination table; *review attachments.
Also please advice if we want to replicate those entries in the database which is hosted by another server 'Server2' with same DB name 'HR' and same tables. Can we use 'Linked Server' feature to do this ?
both server are in the same domain as well.