我正在研究从当前产品表到新表的数据迁移。我已经准备并测试了基于 [Keycol] 和 [col8] (datetime) 的批量插入脚本。此次进口预计将持续10天左右。
--old table
SELECT TOP (1000) [Keycol],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10]
FROM [dbo].[oldtable]
--newtable with identity column
SELECT TOP (1000) [NewIdentitycol],[Keycol],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10]
FROM [dbo].[newtable]
我正在 oldtable 上准备插入/更新/删除触发器,以在开始导入后捕获任何插入/更新/删除操作,并在 newtable 中执行相同的操作。
我对触发器很陌生,我在 Google 中看到的大多数参考文献都只是跟踪任何一列的更新。我基本上想编写触发器来捕获对跟踪表中任何列的更新,如果该记录已导入到新表中,则应用该更新。
我参考了https://www.sqlshack.com/solve-identity-crisis-sql-server/
插入看起来很简单,但不确定我在更新/删除中尝试的内容是否适合我的情况。我要做POC,有没有更好的方法来处理这个问题?请告诉我。
CREATE TRIGGER Migration_trigger
ON dbo.oldtable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
-- INSERTS ONLY
INSERT INTO dbo.newtable
([Keycol],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10])
SELECT Inserted.[Keycol],Inserted.[col2],Inserted.[col3],Inserted.[col4],Inserted.[col5],Inserted.[col6],Inserted.[col7],Inserted.[col8],Inserted.[col9],Inserted.[col10]
FROM Inserted
LEFT JOIN Deleted
ON Deleted.[Keycol] = Inserted.[Keycol]
WHERE Deleted.[Keycol] IS NULL;
-- DELETE ONLY already imported record
IF(SELECT [Keycol] from dbo.newtable n
join inserted on n.[keycol] = inserted.[keycol]
join deleted on inserted.[keycol] = Deleted.[keycol] where n.[Keycol]= Deleted.[Keycol]) IS NOT NULL
BEGIN
DELETE
FROM dbo.newtable
WHERE newtable.[Key] IN (
SELECT Deleted.[Keycol]
FROM Deleted
LEFT JOIN Inserted
ON Deleted.[Keycol] = Inserted.[Keycol]
WHERE Inserted.[Keycol] IS NULL);
END
-- UPDATE ONLY already imported record
IF(SELECT [Keycol] from dbo.newtable n
join inserted on n.[keycol] = inserted.[keycol]
join deleted on inserted.[keycol] = Deleted.[keycol] where n.[Keycol]= Deleted.[Keycol] and [Keycol]=inserted.[Keycol] ) IS NOT NULL
BEGIN
UPDATE dbo.newtable
SET [Keycol] = Inserted.[Keycol],[col2]=Inserted.[col2],[col3]=Inserted.[col3],[col4]=Inserted.[col4],[col5]=Inserted.[col5],[col6]=Inserted.[col6],
[col7]=Inserted.[col7],[col8]=Inserted.[col8],[col9]=Inserted.[col9],[col10]=Inserted.[col10]
FROM Inserted
INNER JOIN Deleted
ON Deleted.[Keycol] = Inserted.[Keycol]
INNER JOIN dbo.newtable
ON newtable.[Keycol] = Inserted.[Keycol]
END
END
谢谢