Checking to see if row data has changed

Question

My Goal: Is to move data from one table to another if the row has been updated or a new row has been Inserted.

I have a table I needs certain fields from. I need to know if the row has been updated or inserted. The Source table does not have any Timestamp fields. I'm using MSSQL2008. The data is coming from a client and they are controlling the tables and replication.

I thought I had it figure out using the new Merge function for MSSQL 2008 but it updates all rows whether or not anything has changed. This is normally not a big problem but I have to add a timestamp fields. My modified time fields will be update whether or not the row has been updated.

So I need a way to complete my above goal. I'm not a great SQL expert so as you can see I'm struggling any help would be great.

USE NaylorAequor
DECLARE CurretDate GetDate();

MERGE Aequor_SLA_Ads AS Target

USING (select AWA.AdOrderID,emp.FirstName, emp.LastName,AWA.VendorID,AO.OrderDate,AO.SaleStatusID,A.AdColorId,AO.PublicationID,AWA.DateAssigned,AWA.DateAdCompleted 
from AdWorkAssignMent as AWA, Employee as emp, AdOrder AS AO,Ad as A
WHERE VendorId = 'Aequor' AND emp.EmployeeID = AWA.EmployeeID AND AWA.AdOrderId = AO.AdOrderID AND AO.AdId = A.AdId) AS Source

ON (Target.AdOrderID = Source.AdOrderID) 

WHEN MATCHED THEN
         UPDATE SET 
            Target.AdOrderID =Source.AdOrderID,
            Target.FirstName = Source.FirstName,
            Target.LastName =Source.LastName,
            Target.VendorID =Source.VendorID,
            Target.OrderDate =Source.OrderDate,
            Target.SaleStatusID =Source.SaleStatusID,
            Target.AdColorId =Source.AdColorId,
            Target.PublicationID =Source.PublicationID,
            Target.DateAssigned =Source.DateAssigned,
            Target.DateAdCompleted =Source.DateAdCompleted,
            Target.AequorModifiedDateTime = GetDate()


WHEN NOT MATCHED BY TARGET THEN
      INSERT (AdOrderID,FirstName,LastName,VendorID,OrderDate,SaleStatusID,AdColorId,PublicationID,DateAssigned,DateAdCompleted,AequorDateTime,AequorModifiedDateTime)

     VALUES (Source.AdOrderID, Source.FirstName,Source.LastName,Source.VendorID, Source.OrderDate,Source.SaleStatusID,Source.AdColorId,
                Source.PublicationID,Source.DateAssigned,Source.DateAdCompleted,GetDate(),GetDate())

OUTPUT $action, Inserted.*, Deleted.*;

Answer 1

Just as an addon to Lamak's answer because these inequality comparisons can get a bit tedious particularly where the columns are nullable you might want to replace them with NOT EXISTS(SELECT Source.* INTERSECT SELECT Target.*)

Example usage below

declare @t1 table
(
id int,
col2 int NULL
)

declare @t2 table
(
id int,
col2 int NULL
)

INSERT INTO @t1 VALUES(1, NULL),(2,NULL)
INSERT INTO @t2 VALUES(1, NULL),(2,NULL), (3,NULL)

MERGE @t1 AS Target
USING  @t2 AS Source
ON (Target.id = Source.id) 
WHEN MATCHED AND NOT EXISTS(SELECT Source.* INTERSECT SELECT Target.*) THEN
         UPDATE SET 
            Target.id =Source.id
WHEN NOT MATCHED BY TARGET THEN
      INSERT (id)
      VALUES (id)
OUTPUT $action, Inserted.*, Deleted.*;