Clearing the Master Data Services Staging Tables

< Back to Insights
Insights  <  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.


Continue the Conversation with Our Team
Get in touch with us.

Contact Us