我正在研究从当前产品表到新表的数据迁移。我已经准备并测试了基于 [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
谢谢
这几乎是正确的,但是
IF (SELECT
如果有多行,则将会失败,并且在任何情况下都是不必要的(如果没有行,则什么也不会发生)。此外
WHERE NOT EXISTS
代替LEFT JOIN...IS NULL
,通常会更快。DELETE
您可以使用它来EXCEPT
使其更具可读性。UPDATE
您不需要更新主键,因为无论如何您都会加入。另外,不要在第一行使用完整的表名,仅使用别名
您还可以使用
MERGE
您不需要从头开始编写批量传输和触发代码。让一切正确、正确处理错误情况并使整个过程可恢复可能很棘手。
Michael J Swart 有一个易于阅读的系列,从在线修改表开始 – 第 1 部分:迁移策略
总体思路和你的一样:
他在 AdventureWorks 示例中使用单独的触发器进行插入、更新和删除,因为这使代码更简单:
小的改进是可能的,但基本实现作为工作模板是合理的。我会将以下内容添加到每个触发器的开头,例如:
确保您阅读并理解该系列的所有五个部分。它们很短。