存盤程序比較兩個表并插入/更新值

假設我有 2 個表：表 A，表 B

表 A 的樣本記錄

表 B 的樣本記錄

我想在存盤程序中做什么：

1. 比較表 B 中的StuId&ExamId是否存在于表 A 中？
   • 如果存在，將值插入Status= 'remain'
   • 如果不存在，則向表 B 插入記錄，并且Status= 'new'
2. 比較分數Listening, Writing, Reading, TotalMarks
   • 如果表 A 中的標記與表 B 相同，則將值插入到Remarks= 'none'
   • 如果ListeningA 表中的標記與 B 表中的標記不同，則將 B 表中的標記更新到 A 表中，并將值插入到Remarks= 'Ori lis % = ' (A 表中的標記) 'New lis % = ' (標記來自表 B)
   • 如果Writing表A中的標記與表B中的標記不同，則將表B中的標記更新到表A，并將值插入到Remarks=備注 'Ori wri％=' （來自表A的標記） '新wri％=' （來自表 B 的標記）
   • 如果ReadingA 表中的標記與 B 表中的標記不同，則將 B 表中的標記更新到 A 表中，并將值插入到Remarks= 備注 'Ori rea % = ' (A 表中的標記) '新 rea % = ' （來自表 B 的標記）
   • TotalMarks 相同

EXEC 存盤程序后表 A 的預期結果

我設法做到了：

• 向表 A 插入新記錄
• 插入狀態 = 新

代碼：

DECLARE @StatusRem VARCHAR(20) = 'remain'
DECLARE @StatusNew VARCHAR(20) = 'new'
DECLARE @Remarks VARCHAR(100) = ' '

INSERT INTO Table_A (StuId, ExamId, Name, Listening, Writing, Reading, TotalMarks, Status, Remarks)         
    SELECT 
        StuId, ExamId, Name, Listening, Writing, Reading,    
        TotalMarks, @StatusNew, @Remarks
    FROM 
        Table_B b 
    LEFT JOIN 
        table_A a ON b.StuId = a.StuId 
                  AND b.ExamId = a.ExamId
    WHERE 
        a.StuId IS NULL AND a.ExamId IS NULL

有沒有人知道我應該如何繼續插入“保留”狀態、比較和更新標記并插入備注？

感謝你的幫助！

uj5u.com熱心網友回復：

為了討論和演示，我們必須有表格而不是關于表格的故事。下次您應該發布查詢以創建表并插入示例資料，如下所示

-- DDL DML
use tempdb
GO

DROP TABLE IF EXISTS TA
GO

CREATE TABLE TA (
    StuId        NVARCHAR(100),
    ExamId       NVARCHAR(100),
    Name         NVARCHAR(100),
    Listening    INT          ,
    Writing      INT          ,
    Reading      INT          ,
    TotalMarks   INT          ,
    Status       NVARCHAR(100),
    Remarks      NVARCHAR(MAX)
)
GO

INSERT TA (StuId, ExamId, Name, Listening, Writing, Reading, TotalMarks, Status, Remarks)
VALUES
('S001', 'EX123', 'Amanda', 70, 85, 88, 81, null, null),
('S002', 'EX150', 'Elisa' , 60, 74, 52, 62, null, null)
GO

DROP TABLE IF EXISTS TB
GO

CREATE TABLE TB (
    StuId        NVARCHAR(100),
    ExamId       NVARCHAR(100),
    Name         NVARCHAR(100),
    Listening    INT          ,
    Writing      INT          ,
    Reading      INT          ,
    TotalMarks   INT          
)
GO

INSERT TB (StuId, ExamId, Name, Listening, Writing, Reading, TotalMarks)
VALUES
('S001', 'EX123', 'Amanda'  , 70, 85, 88, 81),
('S002', 'EX150', 'Elisa'   , 65, 74, 52, 64),
('S003', 'EX213', 'Derick'  , 80, 78, 92, 83),
('S004', 'EX334', 'Jonathan', 43, 65, 69, 59)
GO

SELECT * FROM TA
SELECT * FROM TB
GO

現在我們可以開始討論并提出解決方案

1.比較表B中的StuId & ExamId是否存在于表A中？

MERGE dbo.TA AS tgt
USING (
    SELECT StuId, ExamId, Name, Listening, Writing, Reading, TotalMarks
    FROM TB
    ) as src
ON (tgt.StuId  = src.StuId and tgt.ExamId   = src.ExamId )
WHEN MATCHED
    THEN UPDATE SET tgt.Status = 'remain'
WHEN NOT MATCHED
    THEN INSERT (StuId, ExamId, Name, Listening, Writing, Reading, TotalMarks, Status)
    VALUES (src.StuId, src.ExamId, src.Name, src.Listening, src.Writing, src.Reading, src.TotalMarks, 'new')
OUTPUT Inserted.StuId, Inserted.ExamId, Inserted.Name, Inserted.Listening, Inserted.Writing, Inserted.Reading, Inserted.TotalMarks, Inserted.Status;
GO 

2. 比較聽力、寫作、閱讀、TotalMarks 的分數

如果您想同時獲得這兩個條件，那么您可以使用以下完整的解決方案，使用單個 MERGE 查詢

MERGE dbo.TA AS tgt
USING (
    SELECT StuId, ExamId, Name, Listening, Writing, Reading, TotalMarks
    FROM TB
    ) as src
ON (tgt.StuId  = src.StuId and tgt.ExamId   = src.ExamId )
WHEN MATCHED
    THEN UPDATE SET tgt.Status = 'remain', 
        tgt.Remarks = CASE
            WHEN src.Listening = tgt.Listening and src.Writing = tgt.Writing and src.Reading = tgt.Reading and src.TotalMarks = tgt.TotalMarks THEN 'none'
            ELSE  IIF(src.Listening  != tgt.Listening ,'Ori lis % = '   CONVERT(NVARCHAR(100),src.Listening )   '; New lis % = '    CONVERT(NVARCHAR(100),tgt.Listening )   ';','')
                  IIF(src.Writing    != tgt.Writing   ,'Ori wri % = '   CONVERT(NVARCHAR(100),src.Writing   )   '; New wri % = '    CONVERT(NVARCHAR(100),tgt.Writing   )   ';','')
                  IIF(src.Reading    != tgt.Reading   ,'Ori rea % = '   CONVERT(NVARCHAR(100),src.Reading   )   '; New rea % = '    CONVERT(NVARCHAR(100),tgt.Reading   )   ';','')
                  IIF(src.TotalMarks != tgt.TotalMarks,'Ori Ttl % = '   CONVERT(NVARCHAR(100),src.TotalMarks)   '; New Ttl % = '    CONVERT(NVARCHAR(100),tgt.TotalMarks)   ';','')
        END
WHEN NOT MATCHED
    THEN INSERT (StuId, ExamId, Name, Listening, Writing, Reading, TotalMarks, Status)
    VALUES (src.StuId, src.ExamId, src.Name, src.Listening, src.Writing, src.Reading, src.TotalMarks, 'new')
OUTPUT Inserted.StuId, Inserted.ExamId, Inserted.Name, 
    Inserted.Listening  as Listening_Ori  , src.Listening  as Listening_New  ,
    Inserted.Writing    as Writing_Ori    , src.Writing    as Writing_New    ,
    Inserted.Reading    as Inserted_Ori   , src.Reading    as Inserted_New   ,
    Inserted.TotalMarks as TotalMarks_Ori , src.TotalMarks as TotalMarks_New ,
    Inserted.Status, Inserted.Remarks;
GO 

SELECT * FROM TA
SELECT * FROM TB
GO

標籤：sql sql服务器 存储过程 sql更新

上一篇：選擇到臨時表

下一篇：如何獲取與另一個表沒有任何外鍵的父表的ID