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.
|@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.
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.