Forum Discussion
Truncate and load data
Hi Expert after truncate and load data ... how we can roll back to original data
The enhancement for CBDW.DIM.ProcessDimDesk has been completed as per MKTTREX-4949 requirements.
The stored procedure has been updated to ensure all possible desk values are loaded into dimDesk, instead of the previous limited subset.
Logic has been restructured into a branched population using the four sources in priority order:
(a) AMI_Staging.dbo.Deals → (b) dimSummitSupplementary → (c) dimMurexSupplementary → (d) AMI_Staging.dbo.SUMMIT_Errors.
Each subsequent source only inserts desks not already present in earlier sources.
A new column DeskSource has been added to dimDesk and populated to indicate the originating data source for each desk.
A default row (-1, 'Unknown', 'Unknown') has been added as required.
Validation confirmed:
dimDesk previously contained fewer desks than source systems
All active desks were unique
Missing desks are now captured from all four source systems
All changes are now aligned with the requirement for a complete and referential desk dimension. Let me know if further adjustments or validation outputs are required.
1 Reply
Hi,
a TRUNCATE can only be rolled back if it was executed inside an explicit BEGIN TRANSACTION block.
If the TRUNCATE was executed normally (which is usually the case), it commits as a data definition operation immediately and cannot be undone. In that case, only a database backup or reload can restore the original data can help.
These are the ways SQL Server can roll back a TRUNCATE- TRUNCATE direkt and stand alone can not be rolled back (exception BEGIN TRANSACTION)
- Only a database restore (full, differential, log) can recover the previous state
- Or reload the old data from an external source
Sorry for having no other feedback on this.
Best
