Batch macro in-database Write Data In-DB error

[albert_alaluf]

Hello all,

I have batch macro runs in in-database. I use within-database, because the data is not needed to manipulation. Basically outside of the macro, I used generate rows to create 24 months, then within formula tool I create 24 dynamically created SQL script. My batch macro reads each line, process and write to the table. The problem is after the first iteration, it returns an error saying Table already exists. I'm not surprised. After read and process the first iteration, the table is created, because I have CREATE TABLE option in Write Data In-DB tool. 

So, my goal is automate this. To create the table from the scratch, and then write every iteration to the table. I have one option is, to bring data to my memory and write to the table when all iterations are completed. However this is big data and no need to bring it to my memory, and then back to the server.

Another option is, to add dynamic SQL, run a script, pull one row,  and create the table and then delete the data from POST SQL. This is another alternative and looks like OK, but I'm looking much smarter solutions. Please note that the table doesn't exist on the server, and it will be created during the run.

Any suggestions?

Albert

[PanPP · Accepted solution]

Hi @albert_alaluf 

Are you able to create the table before running the workflow and then just append the data to the table once scheduled with the In-DB tools? 

The solutions you mentioned would be the alternate solution if you cannot create a table prior to running the workflow. 

[albert_alaluf]

Hi @PanPP ,

It looks like you think like as I think. Add another process to the workflow, use in memory dynamic input, run SQL for 1 row data only, then delete from POST SQL, and continue to run. In that way, inside the batch macro will be only append the data. I'm on same page with you, looks like this is the solution. I'm just looking for another way to do this inside the macro. But because of this is a batch, it looks like it will not be that easy.

[PanPP]

Hi @albert_alaluf 

You can also create a separate workflow that is triggered first followed by the batch macro workflow. This first workflow would create a table with no data.

Here is a blog that would help you organize workflow orchestration.

This solution would be straightforward compared to modifying the batch and running more SQL scripts. 

Hope this helps.