Page Comparison

...

Code Block

language	sql
title	Truncate statements

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

Generate truncate statements in PostgreSQL:

Code Block

language	sql
title	Truncate Data Location Tables for Postgres

select 'truncate table SCHEMA.' || tablename || ';' generated_statements
from pg_catalog.pg_tables 
where schemaname = 'SCHEMA' 
and tablename not like 'dl_%'
and tablename like '%ENTITYNAME'
order by substr(tablename,3), tablename;
;

Info
This technique only removes the data, but does not remove the data structures (tables). For that purpose, you must drop the data location. This is great when you really want to start completely fresh, but you must use this option carefully. This is done from the Data Locations tab in the workbench.

...

Page Properties

hidden	true

Related issues

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

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

Generate truncate statements in Oracle like this:

Code Block

language	sql
title	Truncate Data Location Tables

select 'truncate table EXT_INT_RDM.' || table_name || ';'
from all_tables 
where
  owner = 'EXT_INT_RDM'           /* 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 '%STORE'    /* edit these filters as needed          */
order by substr(table_name,3), table_name;

It will give you runnable SQL like this:

Code Block

language	sql
title	Truncate statements

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

Info
This technique only removes the data, but does not remove the data structures (tables). For that purpose, you must drop the data location. This is great when you really want to start completely fresh, but you must use this option carefully. This is done from the Data Locations tab in the workbench.

Versions Compared

Old Version 1

New Version 2

Key

Generate truncate statements in PostgreSQL:

Generate truncate statements in Oracle like this:

Related articles