score:1

Accepted answer

First, insert new teacherID and classID combination. Then update rows from Students table.

CREATE TABLE #Students(
    ClassID     INT,
    TeachedID   INT,
    isActive    BIT
)
CREATE TABLE #StudentsTemp(
    ClassID     INT,
    TeachedID   INT,
    isActive    BIT
)
INSERT INTO #Students VALUES
(1, 2, 1), (1, 12, 1), (1, 13, 0), (5, 10, 1);
INSERT INTO #StudentsTemp VALUES
(1, 2, 1), (1, 13, 1), (1, 6, 1);


UPDATE s
    SET s.isActive = ISNULL(t.isActive, 0)
FROM #Students s
LEFT JOIN #StudentsTemp t
    ON t.ClassID = s.ClassID
    AND t.TeachedID = s.TeachedID

INSERT INTO #Students
SELECT
    ClassID,
    TeachedID,
    isActive
FROM #StudentsTemp t
WHERE
    NOT EXISTS(
        SELECT 1
        FROM #Students
        WHERE
            ClassID = t.ClassID
            AND TeachedID = t.TeachedID
    )


SELECT * FROM #Students

DROP TABLE #Students
DROP TABLE #StudentsTemp

RESULT

ClassID     TeachedID   isActive
----------- ----------- --------
1           2           1
1           12          0
1           13          1
5           10          0
1           6           1

Read More

score:1

Have you tried using a Merge statement?

https://msdn.microsoft.com/en-us/library/bb510625.aspx

score:1

Try something like this:

MERGE Students AS T
USING Students_Temp AS S
ON (T.ClassId = S.ClassID AND T.TeacherID = S.TeacherID) 
WHEN NOT MATCHED BY TARGET
    THEN INSERT(ClassID, TeacherID, IsActive) VALUES(S.ClassID, S.TeacherID, S.IsActive)
WHEN NOT MATCHED BY SOURCE
    THEN UPDATE SET T.IsActive = 0
WHEN MATCHED 
    THEN UPDATE SET T.IsActive = S.IsActive

More questions

More questions with similar tag