Checking if row data has changed

Question

My goal: Is moving data from one table to another if the row is updated or a new row is added.

I have a table in which I need certain fields. I need to know if the row has been updated or inserted. The source table has no Timestamp fields. I am using MSSQL2008. Data comes from the client, and it controls tables and replication.

It seemed to me that I understood using the new Merge function for MSSQL 2008, but it updates all rows regardless of whether it has changed or not. This is usually not a big problem, but I have to add timestamp fields. My changed time fields will be updated regardless of whether the row is updated.

So I need a way to complete my task. I'm not a big SQL expert, as you can see I'm struggling to help.

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

You need to add a constraint that you want to update only if some values ​​are different. Therefore, you need to change this part of the request to something like this:

 WHEN MATCHED AND ( Target.FirstName != Source.FirstName OR Target.LastName != Source.LastName OR Target.VendorID != Source.VendorID OR Target.OrderDate != Source.OrderDate OR Target.SaleStatusID != Source.SaleStatusID OR Target.AdColorId !=Source.AdColorId OR Target.PublicationID !=Source.PublicationID OR Target.DateAssigned !=Source.DateAssigned OR Target.DateAdCompleted !=Source.DateAdCompleted) 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() 

In this case, the comparison assumes that each field is not NULL, if it is not, you need to add this to the logic (a ISNULL or something like that)

Answer 2

Just like the Lamak addon responds because these inequality comparisons can be a bit tedious, especially if the columns are NULL, you can 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.*;