MDS: How to truncate an Entity-table?

Here’s how you can quickly (with using the GUI or Excel Add-in) truncate an Entity-table in MDS by using the accompanied Staging-table (stg_*):

MDS-truncate-enitity-table









MDS needs the Code-ids together with the ImportType = 4 to delete records via staging.
Basically the first step is to populate the staging table by using the MDS view (which contains the current set of records and thus the Code-ids), after that you’ll need to execute the accompanied MDS stored procedure to process these changes.
Important: make sure that both BatchTag’s are identical!



Here’s the T-SQL code:

TRUNCATE TABLE stg.[TableName_Leaf]

INSERT INTO stg.[TableName_Leaf] (ImportType,ImportStatus_ID,BatchTag,Code)
SELECT
4 AS ImportType
,0 AS ImportStatus_ID
,'Delete-20151102' AS BatchTag
,Code
FROM mdm.JOBS_TableName

SELECT * FROM stg.[TableName_Leaf]

EXEC stg.[udp_TableName_Leaf] @VersionName = 'VERSION_1', @LogFlag = 0, @BatchTag = 'Delete-20151102'



More info about working with MDS staging tables (and ETL):
http://www.sqlchick.com/entries/2013/2/16/importing-data-into-master-data-services-2012-part-1.html
http://www.mattmasson.com/2013/06/bulk-loading-into-mds-using-ssis/
http://www.sqlservercentral.com/blogs/simon_says/2014/01/28/master-data-services-for-the-beginner-part-03/








Advertisements

One thought on “MDS: How to truncate an Entity-table?

  1. Pingback: HOW TO: Delete records from your MDS entity table | Clint Huijbers' Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s