1679
Views
7
Comments
Solved
Best way to truncate tables?
Question

Hi,


I need to empty a table with about 9 million records every 2 weeks.  I tought I could simply truncate the table with a query, but that doesn't work.


What is the most easy and fastest way to clear the records?


Regards,


Raymond



mvp_badge
MVP
Rank: #26
Solution

Hi Raymond,

There are two options:

1. Use Advanced SQL with the following command: DELETE FROM {TABLENAME}, but this is time consuming and will take up a lot of your resources. You might need to cut this process into smaller steps (DELETE TOP xxx FROM {TABLENAME}) within a loop until the table is empty to keep within the boundaries of your resources i.e. Transaction log doesn't grow outside your available database size, causing your environment to become unavailable.

2. Use Advanced SQL with the following command: TRUNCATE TABLE {TABLENAME}, but you need to extend the rights of your user on the database with enough rights to execute TRUNCATE / DROP statements on this specific table. 

Be aware that option 2 has a security risk that data might get lost when you grant TRUNCATE rights on all tables, because TRUNCATE doesn't keep track of changes in the transaction log.

Kind regards,

Remco Dekkinga

Rank: #330

Raymond Lips wrote:

Hi,


I need to empty a table with about 9 million records every 2 weeks.  I tought I could simply truncate the table with a query, but that doesn't work.


What is the most easy and fastest way to clear the records?


Regards,


Raymond



You can create a job in the database.

Regards,

Ib


mvp_badge
MVP
Rank: #26

Hi Raymond,

I've some questions in return, because there can be a lot of reasons why it is not working, but there are also a lot of solutions.

Can you explain what you tried and why is the truncate is not working? 

Do you want to clear from OutSystems and did you try the Advanced Query?

Do you want to clear from the database directly? Are you in the OS Cloud or on premise?

Kind regards,

Remco Dekkinga

mvp_badge
MVP
Rank: #26
Solution

Hi Raymond,

There are two options:

1. Use Advanced SQL with the following command: DELETE FROM {TABLENAME}, but this is time consuming and will take up a lot of your resources. You might need to cut this process into smaller steps (DELETE TOP xxx FROM {TABLENAME}) within a loop until the table is empty to keep within the boundaries of your resources i.e. Transaction log doesn't grow outside your available database size, causing your environment to become unavailable.

2. Use Advanced SQL with the following command: TRUNCATE TABLE {TABLENAME}, but you need to extend the rights of your user on the database with enough rights to execute TRUNCATE / DROP statements on this specific table. 

Be aware that option 2 has a security risk that data might get lost when you grant TRUNCATE rights on all tables, because TRUNCATE doesn't keep track of changes in the transaction log.

Kind regards,

Remco Dekkinga

Rank: #1239

Thanks Remco,

The solution I am looking for aparently doesn't exist. Your 2nd option seems to be the next best thing.

Kind Regards,

Raymond

Rank: #167

Hi guys, what about if the Table has FKs? 

What's the best approach here?