Brian Berry | March 25, 2011
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!
