SQL Server 找出某時間的異動資料(資料交換)

問題

最近同事詢問要把有異動的資料轉到另一個資料庫(Sybase)去，是否有什麼好的方式呢?
想了一下，最簡單的方式就是找那某個異動時間之後的資料就可以了。
但是，那 … 被刪除的資料要如何找出來呢?

研究與練習

想了一下，似乎可以透過 Database Snapshot 來達成。
測試的資料庫可以從 AdventureWorks sample databases Download 下載。

建立 Database Snapshot

之所以要建立 資料庫快照集 是因為，如果到了要轉出異動資料時，將 快照裡的資料表 與 原有資料庫中的資料表 Join 就可以知道那些是被刪除的資料。

CREATE DATABASE AdventureWorks_dbss1100 ON  
( NAME = AdventureWorks2017, FILENAME =   
'C:\temp\AdventureWorks_dbss1100.ss' )  
AS SNAPSHOT OF AdventureWorks2017;  
GO  

資料操作

以下分別進行 Inser , Update & Delete

-- INSERT DATA
DECLARE @BEID INT;
DECLARE @MOD_DATE DATETIME = '2020/12/17 11:20';
INSERT INTO [Person].[BusinessEntity]
VALUES(NEWID(), @MOD_DATE);

SELECT @BEID = SCOPE_IDENTITY();

INSERT [Person].[Person] 
([BusinessEntityID], [PersonType], [NameStyle], [Title]
, [FirstName], [MiddleName], [LastName], [Suffix]
, [EmailPromotion], [AdditionalContactInfo], [Demographics]
, [rowguid], [ModifiedDate]) 
VALUES 
(@BEID, N'EM', 0, NULL
, N'Rainmaker', N'D', N'Ho', NULL
, 1, NULL, NULL
, NEWID(), @MOD_DATE);

-- MODIFY DATA
UPDATE [Person].[Person]
SET MiddleName = 'D', 
[ModifiedDate] = GETDATE()
Where BusinessEntityID = 1;

-- Delete Data
Delete FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 75123;

所以如果要找出 2020/12/17 11:00 之後的異動，就可以依 來找，

-- 找出某時間點之後差異的資料
SELECT * FROM [Person].[Person]
WHERE ModifiedDate > '2020/12/17 11:00';

刪除的資料，就可以用 快照裡的資料表 與 原有資料庫中的資料表 Join ，如下，

-- 那被刪除的資料呢?
SELECT *
FROM AdventureWorks_dbss1100.Sales.SalesOrderHeader SN
LEFT JOIN Sales.SalesOrderHeader SO
ON SN.SalesOrderID = SO.SalesOrderID
WHERE SO.SalesOrderID IS NULL

那如果要找新增的資料呢?
一樣的方式，只是這次是要找在 SNAPSHOT 資料表為 Null 。
以上面有針對 Person.Person 新增一筆資料，所以找出它的方式如下SQL，

SELECT SN.BusinessEntityID, PS.*
FROM AdventureWorks_dbss1100.Person.Person SN
RIGHT JOIN Person.Person PS
ON SN.BusinessEntityID = PS.BusinessEntityID
WHERE SN.BusinessEntityID IS NULL

所以在每次匯出異動資料後，就可以再建立那時間點的 資料庫快照 ，之後再跟原有資料表 Join 就可以了哦!

參考資料

AdventureWorks sample databases Download
SQL Server Database Snapshots for Data Comparison
資料庫快照集