Assume I have 2 table: Table A, Table B

Sample record for Table A

Sample record for Table B

What I want to do in a stored procedure:

1. Compare StuId & ExamId in Table B exists in Table A?
   • if exists, insert value to Status = 'remain'
   • if not exists, insert record to Table B, and Status = 'new'
2. Compare marks of Listening, Writing, Reading, TotalMarks
   • if marks in Table A same with Table B, insert value to Remarks = 'none'
   • if Listening marks in Table A is not same with Table B, update the marks from Table B to Table A, and insert value to Remarks = 'Ori lis % = ' (marks from Table A) ' New lis % = ' (marks from Table B)
   • if Writing marks in Table A is not same with Table B, update the marks from Table B to Table A, and insert value to Remarks = Remarks 'Ori wri % = ' (marks from Table A) ' New wri % = ' (marks from Table B)
   • if Reading marks in Table A is not same with Table B, update the marks from Table B to Table A, and insert value to Remarks = Remarks 'Ori rea % = ' (marks from Table A) ' New rea % = ' (marks from Table B)
   • same for TotalMarks

Expected result for Table A after EXEC stored procedure

What I had managed to do:

• insert new record to Table A
• insert status = new

Code:

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

Does anyone have an idea on how I should continue to insert 'remain' status, compare & update the marks and insert the remarks?

Appreciate your help!

CodePudding user response：

For the sake of the discussion and demo we must have tables and NOT stories about tables. Next time you should publish queries to create the tables and insert the sample data, as bellow

-- 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

And now we can start the discussion and present solution

1.Compare StuId & ExamId in Table B exists in Table 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. Compare marks of Listening, Writing, Reading, TotalMarks

If you want to get both condition together then you can use the following full solution using single MERGE query

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