In order to clear the Staging tables from Master Data Services, it is easiest to simply call a stored procedure.
exec mdm.udpStagingClear @User_ID,@StagingType_ID,@DeleteType_ID, @ModelName, @Batch_ID
To get your @User_ID, refer to the mdm.tblUser table.
@StagingType_ID is required, but only used when @DeleteType_ID = 0 or 1
The @DeleteType_ID Parameter controls how the staging table is cleared.
Value | Description | |
@DeleteType_ID | 0 | Delete by Model and User. |
@DeleteType_ID | 1 | Delete by Model and User the staging records that have processed successfully. |
@DeleteType_ID | 2 | Delete by User. |
@DeleteType_ID | 3 | Delete by Batch, including the batch record. |
When @DeleteType_ID = 0 or 1, @ModelName is required and you should pay attention to the @StagingType_ID.
Value | Description | |
@StagingType_ID | 1 | Delete Members |
@StagingType_ID | 2 | Delete Attributes |
@StagingType_ID | 3 | Delete Relationships |
@StagingType_ID | 4 | Delete All |
I prefer to use @DeleteType_Id = 3 so that I clear the tblStgBatch table as well, but @DeleteType_ID=1 is also appealing, as it bulk clears while retaining failed loads. Also, you really should only use this for Initial Data Loads of Master Data. For all other write operations, I recommend the WCF Service.
Enjoy!
Please submit your inquiry or questions using the form below and we’ll get back to you shortly.