Archiving data from Table1 to Table2 upon condition | Sql Server 2017 Express

I have a database with two tables:

Table Student with the following
columns:

Student

StudentID int identity, StudentFN, StudentLN, Active bit, EnrollmentDate


Table ArchivedStudent with the following columns:

ArchivedStudent

ArvchivedStudentID int identity, StudentID int, StudentFN, StudentLN, WithdrawalDate getdate(), ReasonDropped


In the long run, I'd like to schedule automatic updates for the table AcrchivedStudent and move the data from columns StudentID, StudentFN and StudentLN from table Student to table ArchnivedStudent when column Active changes from 1 (true) to 0 (false).

AcrchivedStudent

StudentID, StudentFN

StudentLN

ArchnivedStudent

Here's my start up script that is not working:

update [as]
set [as].StudentID = s.StudentID,
[as].StudentFN = s.StudentFN,
[as].StudentLN = s.StudentLN
from ArchivedStudent [as]

inner join Student s
on [as].StudentID = s.StudentID
where s.Active = 0

go


The issue is that it does not return any results.

Once I'll be able to update table ArchivedStudent, I'd like to delete data of the students whose Active status changed to 0 in the Student table.

ArchivedStudent

This question has not received enough attention.

3 Answers
3

You need two queries:

Insert

Insert into archivedstudent (studentid, student, studentln) select studentid, studentfn, studentln from student where active=0 and studentid not in (select studentid from archivedstudent);


And the delete

Delete from student where studentid in (select studentid from archivedstudent);


Your question still isn't very clear on the process. For example, do you want to allow the student to be deactivated for a certain period of time before they are moved to the archive table or do you want the student to be immediately moved to the archived table once the student is deactivated?

If the latter, this is much easier:

INSERT INTO ArchivedStudent (StudentId, StudentFn, StudentLn, WithdrawalDate)
SELECT S.StudentId, S.StudentFn, S.StudentLn, GETDATE()
FROM Student S
WHERE StudentId = ?

DELETE FROM Student WHERE StudentId = ?


If the former, then that is more challenging and we will require more detail.

What you need is a trigger.SQL Server Trigger After Update for a Specific Value However you should be careful with triggers on large amounts of data, they can hurt performance.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.