I have a stored procedure that does the following:
BEGIN TRANSACTION -- Code to delete updated records from production (dbo) table DELETE FROM [dbo].[factMyTable] WHERE exists (SELECT * FROM [RAW].[MyTable] WHERE [RAW].[MyTable].[refno] = [dbo].[factMyTable].[refno] AND [RAW].[MyTable].[modification_dttm] >= [dbo].[factMyTable].[modification_dttm] ) -- Code to perform the append of incremental records INSERT INTO [dbo].[factMyTable] SELECT [refno] ,[field1] ,[field2] ,[field3] ,[FieldN] ,[modification_dttm] FROM [RAW].[MyTable] -- Truncate stage table and get ready for next load TRUNCATE TABLE [RAW].[MyTable] COMMIT TRANSACTION
As you can see above, I have a truncate command that is contained within a BEGIN/COMMIT transaction block. However I got an error when executing this stored procedure at the insert command, where a field set as NOT NULL was receiving a NULL value. As a result:
- The insertion of records from the RAW table into the dbo table got rolled back; BUT
- The truncation of the RAW table did not roll back.
The idea is that if there is an error with the insertion of data, the truncation shouldn't happen.
According to this article, we can roll back the truncate command, but perhaps my stored procedure is not scripted correctly. Perhaps there is a more direct way of ensuring that the truncation only happens if the insertion returns no errors? How would I go about it?