问题描述
我必须从 temptable 中选择记录并插入或更新到 mastertable.如果更改了单个字段，那么我需要使用以下内容更新我的 history 表:
I have to select the records from temptable and insert or update to mastertable. If a single field is changed, then I need to update my history table with the following:
RecordIDColumnChanged旧值NewValue
在执行从 temptable 到 mastertable 的插入或更新时，由于外键违规(或任何其他错误)而发生的任何错误都应记录到error 表.
While performing the insert or update from the temptable to the mastertable, any errors that's occur due to foreign key violations (or any other errors) should be logged to the error table.
我需要更改历史记录和错误日志，以便如果单个记录失败，我可以再次重新运行该过程.
I need the history of changes and logging of errors, so that if a single record fails, I can rerun the process again.
如何使用 T-SQL 执行此操作?任何代码片段都会有所帮助.
How can I do this using T-SQL? Any code snippets will be helpful.
这是我正在使用的表格列:
Here are the tables columns that I'm working with:
TEMPTABLE
COL1、COL2、COL3、COL4、COL5
TEMPTABLE
COL1, COL2, COL3, COL4, COL5
精通
COL1、COL2、COL3
MASTERTABLE
COL1, COL2, COL3
历史
RecordID、ColumnChanged、OldValue、NewValue
HISTORY
RecordID, ColumnChanged, OldValue, NewValue
错误
错误代码、错误消息
ERROR
ErrorCode, ErrorMsg
我需要使用游标，因为我需要在执行插入或更新时遍历每条记录，并记录在插入或更新期间由于错误数据而发生的任何错误.
I need to use a cursor, as I need to loop through each record when performing the inserts or updates, and to log any errors that happen during the inserting or updating due to bad data.
我需要将所有成功插入或更新的 flagcolumn 标记为Y"，将所有失败的记录标记为N"，以便我可以在更正数据后重新加载它们.
I need to mark the flagcolumn as "Y" for all successful insert or updates and "N" for all the failed records so that I can reload them again after correcting the data.
我已经使用 sqlbulkcopy 将数据加载到 temptable.
I've already used sqlbulkcopy to load the data in to the temptable.
推荐答案
试试这个 -
架构:
CREATE TABLE dbo.HistoryTable
(
HistoryTableID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
PKRecordID INT NOT NULL,
ColumnChanged VARCHAR(50) NOT NULL,
OldValue VARCHAR(10) NOT NULL,
NewValue VARCHAR(10) NOT NULL,
ChangedDate DATETIME NOT NULL DEFAULT (GETDATE())
)
GO
CREATE TABLE dbo.ForeignKeyTableCOL2 (COL2 VARCHAR(10) PRIMARY KEY NOT NULL)
GO
CREATE TABLE dbo.ForeignKeyTableCOL1 (COL1 VARCHAR(10) PRIMARY KEY NOT NULL)
GO
CREATE TABLE dbo.ErrorTable
(
ErrorTableID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
PKRecordID INT NOT NULL,
TableName VARCHAR(50) NOT NULL,
TablePK INT NOT NULL,
ErrorDateTime DATETIME NOT NULL DEFAULT (GETDATE()),
ErrorCode INT NOT NULL,
ErrorMsg VARCHAR(2000) NOT NULL
)
GO
CREATE TABLE dbo.TempTable
(
TempTableID INT IDENTITY(1,1) NOT NULL,
PKRecordID INT NOT NULL,
COL1 VARCHAR(10) NOT NULL,
COL2 VARCHAR(10) NOT NULL,
COL3 VARCHAR(10) NOT NULL,
COL4 VARCHAR(10) NOT NULL,
COL5 VARCHAR(10) NOT NULL,
Success CHAR(1) NOT NULL DEFAULT ('N')
)
GO
CREATE TABLE dbo.MasterTable
(
PKRecordID INT NOT NULL,
COL1 VARCHAR(10) NOT NULL,
COL2 VARCHAR(10) NOT NULL,
COL3 VARCHAR(10) NOT NULL,
COL4 VARCHAR(10) NOT NULL,
COL5 VARCHAR(10) NOT NULL
)
GO
ALTER TABLE dbo.MasterTable WITH CHECK ADD CONSTRAINT FK_MasterTable_ForeignKeyTableCOL1 FOREIGN KEY(COL1)
REFERENCES dbo.ForeignKeyTableCOL1 (COL1)
ALTER TABLE dbo.MasterTable CHECK CONSTRAINT FK_MasterTable_ForeignKeyTableCOL1
ALTER TABLE dbo.MasterTable WITH CHECK ADD CONSTRAINT FK_MasterTable_ForeignKeyTableCOL2 FOREIGN KEY(COL2)
REFERENCES dbo.ForeignKeyTableCOL2 (COL2)
ALTER TABLE dbo.MasterTable CHECK CONSTRAINT FK_MasterTable_ForeignKeyTableCOL2
INSERT dbo.ForeignKeyTableCOL1 (COL1)
VALUES ('A'), ('B'), ('C')
INSERT dbo.ForeignKeyTableCOL2 (COL2)
VALUES ('A'), ('B'), ('C')
INSERT dbo.TempTable (PKRecordID, COL1, COL2, COL3, COL4, COL5)
VALUES
(1, 'A', 'A', 'A', 'A', 'A'),
(2, 'B', 'B', 'B', 'B', 'B'),
(3, 'C', 'C', 'C', 'C', 'C'),
(1, 'D', 'A', 'A', 'A', 'A'),
(1, 'A', 'D', 'A', 'A', 'A'),
(1, 'D', 'D', 'A', 'A', 'A'),
(2, 'A', 'B', 'B', 'B', 'B'),
(3, 'A', 'B', 'C', 'C', 'C'),
(4, 'D', 'D', 'D', 'D', 'D')
查询:
SET NOCOUNT ON;
DECLARE
@PKRecordID INT
, @COL1 VARCHAR(10)
, @COL2 VARCHAR(10)
, @COL3 VARCHAR(10)
, @COL4 VARCHAR(10)
, @COL5 VARCHAR(10)
, @TempTableID INT
, @New_Row XML
, @Old_Row XML
DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
t.TempTableID
, t.PKRecordID
, t.COL1
, t.COL2
, t.COL3
, t.COL4
, t.COL5
, New_Row = CAST('<r><s>' + t.COL1 + '</s><s>' + t.COL2 + '</s><s>' + t.COL3 + '</s><s>' + t.COL4 + '</s><s>' + t.COL5 + '</s></r>' AS XML)
, Old_Row = CAST('<r><s>' + m.COL1 + '</s><s>' + m.COL2 + '</s><s>' + m.COL3 + '</s><s>' + m.COL4 + '</s><s>' + m.COL5 + '</s></r>' AS XML)
FROM dbo.TempTable t
LEFT JOIN dbo.MasterTable m ON t.PKRecordID = m.PKRecordID
OPEN cur
FETCH NEXT FROM cur INTO
@TempTableID
, @PKRecordID
, @COL1
, @COL2
, @COL3
, @COL4
, @COL5
, @New_Row
, @Old_Row
WHILE @@FETCH_STATUS = 0 BEGIN
BEGIN TRY
IF @Old_Row IS NOT NULL BEGIN
UPDATE dbo.MasterTable
SET
COL1 = @COL1
, COL2 = @COL2
, COL3 = @COL3
, COL4 = @COL4
, COL5 = @COL5
WHERE PKRecordID = @PKRecordID
INSERT dbo.HistoryTable
(
PKRecordID
, ColumnChanged
, OldValue
, NewValue
)
SELECT
@PKRecordID
, 'COL' + CAST(new_id AS VARCHAR(5))
, old_value
, new_value
FROM (
SELECT
new_value = n.value('(.)1', 'VARCHAR(10)')
, new_id = 1 + n.value('for $i in . return count(../*. << $i)', 'int')
, old_value = o.value('(.)1', 'VARCHAR(10)')
, old_id = 1 + o.value('for $i in . return count(../*. << $i)', 'int')
FROM (SELECT a = 1) d
CROSS APPLY @New_Row.nodes('/r/s') t(n)
CROSS APPLY @Old_Row.nodes('/r/s') k(o)
) data
WHERE new_id = old_id
AND NULLIF(new_value, '') != NULLIF(old_value, '')
UPDATE dbo.TempTable
SET Success = 'Y'
WHERE TempTableID = @TempTableID
END
ELSE BEGIN
INSERT dbo.MasterTable
(
PKRecordID
, COL1
, COL2
, COL3
, COL4
, COL5
)
SELECT
@PKRecordID
, @COL1
, @COL2
, @COL3
, @COL4
, @COL5
UPDATE dbo.TempTable
SET Success = 'Y'
WHERE TempTableID = @TempTableID
END
END TRY
BEGIN CATCH
INSERT dbo.ErrorTable
(
PKRecordID
, TableName
, TablePK
, ErrorDateTime
, ErrorCode
, ErrorMsg
)
SELECT
@PKRecordID
, 'TempTable'
, @TempTableID
, GETDATE()
, ERROR_NUMBER()
, ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM cur INTO
@TempTableID
, @PKRecordID
, @COL1
, @COL2
, @COL3
, @COL4
, @COL5
, @New_Row
, @Old_Row
END
CLOSE cur
DEALLOCATE cur
这可能对您有帮助:
CREATE TRIGGER ...
ON ...
INSTEAD OF INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @DocumentUID UNIQUEIDENTIFIER
...
DECLARE cur CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
SELECT DocumentUID, ...
FROM INSERTED
OPEN cur
FETCH NEXT FROM cur INTO @DocumentUID, ...
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE
@BeforeChange NVARCHAR(MAX)
, @AfterChange NVARCHAR(MAX)
SELECT
@BeforeChange = (
SELECT *
FROM DELETED
WHERE DocumentUID = @DocumentUID
FOR XML RAW, ROOT
)
, @AfterChange = (
SELECT *
FROM INSERTED
WHERE DocumentUID = @DocumentUID
FOR XML RAW, ROOT
)
IF EXISTS(
SELECT 1
FROM dbo.Documents
WHERE DocumentUID = @DocumentUID
)
BEGIN
INSERT INTO ...
SELECT @BeforeChange, @AfterChange
END
ELSE BEGIN
...
END
FETCH NEXT FROM cur INTO @DocumentUID, ...
END
CLOSE cur
DEALLOCATE cur
END
这篇关于临时表到主表插入或更新审计和日志记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！