舊系統數據遷移至新表,要求冪等(重複執行不產生重複數據)、原子(全成功或全失敗)、可追蹤(返回狀態碼)。
USE [New_WZJW]
GO
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
GO
CREATE PROC [dbo].[成績管理_過程化成績錄入_數據遷移]
@kcId VARCHAR(50) -- 課程 ID 參數
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
-- 核心:NOT EXISTS 保證冪等,避免重複導入
INSERT INTO [web_inputCj_xsxkb]
([xh], [xm], [xxxzdm], [xxxzmc], [cjxzdm], [kcid], [lrsj], [manuAdd])
SELECT
xh, xm, xxxzdm, xxxzmc, cjxzdm, kcid, lrsj, manualAdd
FROM web_inputCj_cjb AS a
WHERE a.kcId = @kcId
AND NOT EXISTS (
SELECT 1 FROM web_inputCj_xsxkb AS b
WHERE a.xh = b.xh AND a.kcId = b.kcid
);
COMMIT TRAN
RETURN 0 -- 成功
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN
-- 可選:記錄錯誤日誌
-- INSERT INTO ErrLog (...) VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), @kcId, GETDATE())
RETURN 1 -- 失敗
END CATCH
END
NOT EXISTS (SELECT 1 FROM Target WHERE PK = Source.PK) 確保同一源數據只導入一次,支援重跑。0=成功,非0=失敗,應用層據此判斷並記錄日誌/告警。DECLARE @Ret INT
EXEC @Ret = [dbo].[成績管理_過程化成績錄入_數據遷移] @kcId = 'CS101'
IF @Ret = 0
PRINT '遷移成功'
ELSE
PRINT '遷移失敗,請檢查錯誤日誌'
WHILE 1=1
BEGIN
BEGIN TRAN
INSERT TOP (5000) INTO Target ...
SELECT ... FROM Source WHERE ... AND NOT EXISTS (...)
IF @@ROWCOUNT = 0
BEGIN
COMMIT TRAN
BREAK
END
COMMIT TRAN
WAITFOR DELAY '00:00:00.05' -- 釋放鎖間隔
END