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.
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
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,
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.
Thank you @mcleavey
I am going to try your solution even if i am beginner and I am not familiar with macros.