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 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)
4 AS ImportType
,0 AS ImportStatus_ID
,'Delete-20151102' AS BatchTag
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):

2 thoughts 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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s