Conditional worflows create or uptade a table

[Foxhound]

Hello,

I have created several worflows with same logic. 

First, I download external data from different sources, then I transform the datas and finally and integrate them into a Database.

So for each worflows, it was about creating a new table in a Database directly with the output Tool.

But, now, to uptade the datas. I don't need to create again these tables, I just need to replace and append the datas. 

So, It's going to be boring to modify for each worflows, and choosing in the output option : Delete and Append Data.

So, I am looking for a method to create like a conditional worflow with 2 parts and only one of the two parts will be executed :

1- One part would create a table if the table not exist

2- An another part would be executed and overwriting the data 

In this way I could save time, and for the next worflows use such a solution.

Thank you for your help.

[fmvizcaino]

Hi @Foxhound ,

You can use the preSQL statement in your output data tool with a conditional query creating the table if there isn't any.

https://www.sqlitetutorial.net/sqlite-create-table/

Something like this:

CREATE TABLE [IF NOT EXISTS] [schema_name].table_name ( column_1 data_type PRIMARY KEY, column_2 data_type NOT NULL, column_3 data_type DEFAULT 0, table_constraints ) [WITHOUT ROWID];

Be aware that this demands that you know your table structure.

Best,

Fernando Vizcaino

[Foxhound]

Thank you @fmvizcaino

So i'm trying you're solution but I am afraid of having to create the table and specify each columns manuelly.

I can have data with more than 100 columns,

[mceleavey]

Hi @Foxhound ,

You can use the method outlined by  @fmvizcaino but wrap the entire process in a macro, you can then auto-create the dynamic elements of the SQL statement using the column names, then feed this into the macro to overwrite the pre-SQL statement. This way you can make the statement dynamic so you won't have to create the column names manually.

M.

[Foxhound]

Thank you @mcleavey

I am going to try your solution even if i am beginner and I am not familiar with macros.