SQL Table - Data Cleanup
Hi Community,
I've created a method in my File Service to do a cleanup for every file load. Currently, I've set it to delete data when LastUpdated date is greater than maxdate. However, I want to do a cleanup for every new file load. Any suggestions or advice on how to do this? Thanks!
Method Cleanup()
{
Set tMaxDate = ""
&SQL(SELECT Max(LastUpdated) into :tMaxDate
FROM MC_Table_Data.Patient)
&SQL(DELETE MC_Table_Data.Patient WHERE LastUpdated<:tMaxDate)
}
Comments
If your goal is to clear the table before each new file load, the retrieved material only shows two documented approaches for removing all rows from a table:
DELETE table-name, which deletes all records in the table. The example shown isDELETE SQLUser.MyStudents. This removes all rows, but it does not reset the RowID counter. [1]TRUNCATE TABLE table-name, which also deletes all records in the table. The example shown isTRUNCATE TABLE SQLUser.MyStudents. This removes all rows and does reset the RowID counter. [1]
So for your case, instead of deleting rows based on LastUpdated < tMaxDate, you would use a full-table delete before loading the next file, such as either:
&SQL(DELETE FROM MC_Table_Data.Patient)
or
&SQL(TRUNCATE TABLE MC_Table_Data.Patient)
The retrieved example also shows that TRUNCATE TABLE is treated as a delete-type statement, and that its main behavioral difference from DELETE in the example is that it resets the RowID counter. [1]
Sources: