question

ChristopherLundy-9013 avatar image
0 Votes"
ChristopherLundy-9013 asked KranthiPakala-MSFT commented

How do I bulk truncate tables using Azure Data Factory?

I want to make a pipeline or take an action to lookup all the tables in an Oracle database and truncate all of them iteratively. I don't want to copy data as we have pipelines for that but I do want to truncate all data.

How can I do that?

azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Shalvin- Thank you for sharing your solution. It is very helpful :)

Hi @christopherlundy-9013,

We still have not heard back from you. Just wanted to check if any of the suggestions were helpful? If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

0 Votes 0 ·
KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered KranthiPakala-MSFT commented

Hi @christopherlundy-9013,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

In order to truncate the oracle table iteratively, then you will have to have place holder where you have a list of tables that you would want to take action. It could be either oracle table or a Azure Blob.

Then you will have to use a Lookup activity to get the list of tables and then pass the output to ForEach activity and inside ForEach activity have another Lookup activity and write a query to truncate the table (the table name passed passed the current iteration of the ForEach activity as @item() ). But please make sure that your query return some result set as SUCCESS or FAIL so that the inner lookup activity doesn't fail. This is because lookup activity expects a response.

Here is the reference doc: Lookup activity in Azure Data Factory and Azure Synapse Analytics

Please Note: When you use query or stored procedure to lookup data, make sure to return one and exact one result set. Otherwise, Lookup activity fails.

Hope this info helps. Do let us know if you have further query.



  • Please don't forget to click on 130616-image.png and upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @christopherlundy-9013,

Just checking in to see if the above suggestion was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

0 Votes 0 ·
Shalvin avatar image
1 Vote"
Shalvin answered

Hi @ChristopherLundy-9013

I would give you a rather simple solution which can do the truncate in a single query single step.

Get the list of tables to truncate comma separated using lookup / parameter etc.

 pipeline().parameters.tables = 'my_table1,my_table3,my_table3'

Create Truncate query by replacing , with ; TRUNCATE TABLE

 query = @concat('TRUNCATE TABLE ', replace(pipeline().parameters.tables, ',', ' CASCADE; TRUNCATE TABLE '), ' CASCADE;')

Run the query using an activity of your choice, eg. lookup.
I am using this approach for SQL Sever. I am kind of confident that it will work everywhere.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.