...
| Code Block |
|---|
| language | sql |
|---|
| title | Truncate Data Location Tables |
|---|
|
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 '%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 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 likeilike '%ENTITYNAME%entityname' /* edit these filters as needed */
order by substr(tablename,3), tablename;
|
Generate truncate statements in SQL Server:
| Code Block |
|---|
| language | sql |
|---|
| title | Truncate statements for SQL Server |
|---|
|
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
; |
It will give you SQL statements to run against the particular database:
| Code Block |
|---|
|
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 |
| 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. |
...