Portal

Context

During development, it's common to want to remove all data from a data location in order to reload it. Use cases are:

• Matching or Consolidation rules have changed, and you want to apply those changes.
• Hard-deleting all the data from the data location.

Principle

The procedure consists of generating 'truncate' SQL statements, which you will then run on your schema. The generation of these statements relies on metadata tables and differs depending on the database you are using.

Generate truncate instructions for Oracle

select 'truncate table MY_SCHEMA.' || table_name || ';' as generated_statements
from all_tables 
where
owner = 'MY_SCHEMA'  /* set this to your data location schema */
and table_name not like 'DL_%' /* do not truncate these system tables  */
and table_name not like 'EXT_%'  /* do not truncate these system tables  */
and table_name like '%ENTITYNAME' /* edit these filters as needed */
order by substr(table_name,3), table_name;
 

Generated statements will look like this:

truncate table MY_SCHEMA.GD_STORE;
truncate table MY_SCHEMA.GE_STORE;
truncate table MY_SCHEMA.GI_STORE;
truncate table MY_SCHEMA.MD_STORE;
truncate table MY_SCHEMA.MI_STORE;
truncate table MY_SCHEMA.SD_STORE;
truncate table MY_SCHEMA.SE_STORE;
 

Generate truncate instructions for PostgreSQL

Run the following query on your schema:

select 'truncate table #my_schema#.' || tablename || ';' as generated_statements
from pg_catalog.pg_tables
where schemaname = '#my_schema#' /* set this to your data location schema */
and tablename not like 'dl_%'  /* do not truncate these system tables  */
and tablename not like 'ext_%' /* do not truncate these system tables  */
and tablename like '%entity_name' /* edit these filters as needed */
order by substr(tablename,3), tablename;
 

Replace #my_schema# with the name of your schema and modify the last filter as per your needs.

Generate truncate instructions for SQL Server

Run the following query on your schema:

SELECT 'truncate table ' + TABLE_NAME +';' + char(10) + 'go'
FROM SEMARCHY_PRODUCT_RETAIL_MDM.INFORMATION_SCHEMA.TABLES  /* amend Database Name to your database */
WHERE TABLE_NAME NOT LIKE 'DL_%' 
AND  TABLE_NAME NOT LIKE 'EXT_%'  /* Don't truncate these Semarchy system tables starting with DL and EXT */
AND  TABLE_NAME LIKE '%BRAND' /* amend this line to meet your needs */
ORDER BY SUBSTRING(TABLE_NAME, 1, 3), TABLE_NAME
;
 

Generated statements for SQL Server will look like this:

truncate table AE_BRAND;
go
truncate table GD_BRAND;
go
truncate table GH_BRAND;
go
truncate table GX_BRAND;
go
truncate table SA_BRAND;
go