Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

How to Truncate a table?

Avatar

Level 2

Hi everyone

 

I am trying to truncate the contents of a custom table in the Adobe Campaign database. I created that table to hold a set of data that is imported on a daily basis from an external file,but I need to truncate it before loading the data set.

 

I know I could use the Update activity to delete records, but that requires an input. I could build that input by querying all data in the target table first and pass it to the Update activity, then the Update activity would delete the records from the incoming population. However, it would be much more efficient to just truncate the full table.

 

Can someone provide guidance on how to do it?

 

Cheers

Hugo

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @hugol_vallejo

To truncate the table please use SQL Code activity under Actions tab and use command:

TRUNCATE TABLE <tableName>

Regards,

Milan

View solution in original post

4 Replies

Avatar

Correct answer by
Community Advisor

Hi @hugol_vallejo

To truncate the table please use SQL Code activity under Actions tab and use command:

TRUNCATE TABLE <tableName>

Regards,

Milan

Avatar

Level 2

Thanks Milan

Never had used the SQL activity, so that was an easy one. Thanks for the quick reply

Avatar

Level 1

Hi,Milan_Vucetic can u tell me the code to truncate schema in sql

 

 

Avatar

Community Advisor

Hi @Rishave,

 

just be carefull here.

Since the intention is to drop all the objects for a schema, why not just drop and recreate that schema with all the required privileges (rather than dropping each and every objects.

drop user <user_name> CASCADE;
then re-create that schema again.

 

if you want to keep the schema but to delete all objects you may try this:

select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX'); 

Please, ensure you are logged into a proper schema.

Cheers,
Milan