How to insert data in database using multiple Insert queries with one dynamic value in all

Question

I have 17 queries like below .I want to execute below queries where eventyear is a variable and it's value needs to updated dynamically. e.g. eventyear=2021 would be passed from steps previous to executing this.

Could someone provide inputs how to achieve this in designer?

insert into stg_tbl(frm,to,event_year,amount) values('ABC','TEST',eventyear,1);
insert into stg_tbl(frm,to,event_year,amount) values('DEF','TEST',eventyear,1);
insert into stg_tbl(frm,to,event_year,amount) values('TEST3','TEST',eventyear,1);
insert into stg_tbl(frm,to,event_year,amount) values('TEST4','TEST',eventyear,1);
insert into stg_tbl(frm,to,event_year,amount) values('XYZ','TEST',eventyear,1);
insert into stg_tbl(frm,to,event_year,amount) values('LMN','TEST',eventyear,1);

Really appreciate the help.

danilang · Answer

Hi @ramna

While it is possible to do this using a batch macro, it is much easier to use the built-in functionality of the download tool to process all the insert statements for you, as in the attached workflow.

Build up a table that corresponds the data that you'd like to insert into your table. The static values from your insert statements go into a text input tool as follows

insert into stg_tbl(frm,to,event_year,amount) values('ABC','TEST',eventyear,1);
insert into stg_tbl(frm,to,event_year,amount) values('DEF','TEST',eventyear,1);
insert into stg_tbl(frm,to,event_year,amount) values('TEST3','TEST',eventyear,1);
insert into stg_tbl(frm,to,event_year,amount) values('TEST4','TEST',eventyear,1);
insert into stg_tbl(frm,to,event_year,amount) values('XYZ','TEST',eventyear,1);
insert into stg_tbl(frm,to,event_year,amount) values('LMN','TEST',eventyear,1);

becomes

The dynamic value event_year is included in the Dynamic data input and then added to every row in the static data using the Append Fields tool.  The output tool then writes the complete data set to your data base.  depending on how you want the data inserted, i.e. append,  overwrite data, create new table,  select one of the options in Output Options dropdown

Dan

InsertData.yxmd

ramna · Answer

pedrodrfaria

Is it possible to use batch macro to achieve this task?

something like in attached picture. Use control paramter then update value then output data (in this, post sql then use insert queries). With this way,is it possible to replace eventyear variable with the actual value while running insert query ?

Also,How shall alteryx interpret that a particular string is variable ? like eventyear needs to be used as is or like @eventyear / %eventyear% in insert queries?

Batch_macro.PNG

pedrodrfaria · Answer

Hi @ramna,

Are you working with the In Database tools? Or are you working with the regular tools and then trying to output them into a database? This should be fairly simple. Let me know if I misunderstood your question and you are trying to do something else. Please open a new post if you have any questions regarding how to connect to the database, most likely it would be via ODBC Driver, which is also something fairly simple.

Pedro.

New Workflow5.yxmd