Clearing the Master Data Services Staging Tables
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!