检查行数据是否已更改 [英] 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.

一个表我需要一些字段。我需要知道行是否已更新或插入。
源表没有任何时间戳字段。我使用MSSQL2008。数据来自客户端，他们正在控制表和复制。

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.

我想我已经确定使用新的合并对于MSSQL 2008，但它更新所有行，无论是否有任何更改。这通常不是一个大问题，但我必须添加一个时间戳字段。无论该行是否已更新，我修改的时间字段都会更新。

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.

因此，我需要一种方法来完成上述目标。我不是一个伟大的SQL专家，所以你可以看到我正在努力任何帮助将是巨大的。

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

推荐答案

您需要添加您只想如果某些值不同，请更新数据。所以你必须改变查询的这部分，像这样：

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()

在这种情况下，比较假设每个字段都不可为空，如果不是这样，添加到逻辑（ ISNULL 或类似的东西）

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)

这篇关于检查行数据是否已更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！