Checking to see if row data has changed

问题:

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.*;

回答1:

You need to add the restriction that you only want to update the data if some of the values are different. So you have to change that part of the query for 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 is assuming that every field is not nullable, if that's not the case, you need to add that to the logic as well (an ISNULL or something like that)

回答2:

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.*;