Blog Archives

Delete full or partial data from MDS entity

I have started Microsoft MDS (Master Data Services) as a new category to share some really quick and helpful solution of problems faced while working. Let me know if you have Questions in comments section.
To delete partial (good number of rows) or full Entity data with MDS database, Manual UI usage is slow and really bad if numbers of rows are good enough.

Use following query and read the comments /* . . . */ to see where you can update, based on your situation to get things done.

DECLARE @DateTimestamp nvarchar(100) = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),GETDATE(),120),' ',''),'-',''),':',''); /*Can be used to have distinct Timestamp in BatchTag of stg entity data push*/

DECLARE @MDSVersion nvarchar(20) = 'VERSION_1';/*Specify latest version of MDS model*/
DECLARE @DeleteBatchTag nvarchar(100);

SET @DeleteBatchTag = 'Delete' +'Entity1' + @DateTimestamp ;
INSERT INTO [stg].[Entity_1_Leaf]
(ImportType, ImportStatus_ID, BatchTag, Code)
SELECT 6 AS ImportType,

/*Import Type 6 will help you to delete the rows even if Entity is referred in other child entities as Domain Based. Don't use 4 import type as its going to put you in viscous cycle of deactivated - activated domain based data.*/

0 AS ImportStatus_ID,
, Code
FROM mdm.Entity_1
/* Use WHERE clause on current data in entity to specify what you want to delete */
WHERE Name Like '%Test%';

EXECUTE [stg].[udp_Entity_1_Leaf] @VersionName = @Version , @LogFlag = 0, @BatchTag = @DeleteBatchTag , @Batch_ID = NULL;