Page Comparison

...

Code Block

language	sql
title	Truncate Data Location Tables

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

...

Code Block

language	sql
title	Truncate statements

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

Generate truncate statements in PostgreSQL:

Code Block

language	sql
title	Truncate Data Location Tables for Postgres

select 'truncate table SCHEMAmy_schema.' || tablename || ';' as generated_statements
from pg_catalog.pg_tables 
where schemaname = 'SCHEMA' 
my_schema'      /* set this to your data location schema */
  and tablename not like 'dl_%'     /* do not truncate these system tables   */
  and tablename not like '%ENTITYNAME'
'ext_%'    /* do not truncate these system tables   */
  and tablename ilike '%entityname' /* edit these filters as needed          */
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.

Generate truncate statements in

...

SQL Server:

select

Code Block

language	sql
title	Truncate Data Location Tables

statements for SQL Server

SELECT 'truncate table EXT_INT_RDM.' ||+ tableTABLE_nameNAME || +';' from all_tables 
where
  owner = 'EXT_INT_RDM'           /* set this+ char(10) + 'go'
FROM SEMARCHY_PRODUCT_RETAIL_MDM.INFORMATION_SCHEMA.TABLES   /* amend Database Name to your data location schemadatabase */
WHERE  and table_name not likeTABLE_NAME NOT LIKE 'DL_%'  /*
doAND not truncate these system tables   */
  and table_name not like TABLE_NAME NOT LIKE 'EXT_%'    /* do notDon't truncate these Semarchy system tables starting with DL and EXT */
AND  and tableTABLE_nameNAME likeLIKE '%STORE%BRAND'      /* editamend thesethis filtersline asto neededmeet your         needs */
orderORDER byBY substrSUBSTRING(table_name,TABLE_NAME, 1, 3), tableTABLE_nameNAME
;

It will give you runnable SQL like thisSQL statements to run against the particular database:

Code Block

language	sql
title	Truncate statementsExample

truncate table EXT_INT_RDM.GD_STOREAE_BRAND;
go
truncate table EXT_INT_RDM.GE_STOREGD_BRAND;
go
truncate table EXT_INT_RDM.GI_STOREGH_BRAND;
go
truncate table EXT_INT_RDM.MD_STORE;
truncate table EXT_INT_RDM.MI_STORE;
GX_BRAND;
go
truncate table EXT_INT_RDM.SD_STORE;
truncate table EXT_INT_RDM.SE_STORE; SA_BRAND;
go

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 2

New Version Current

Key

Generate truncate statements in PostgreSQL:

Related articles

Generate truncate statements in

SQL Server:

Related articles