Use single connection to insert into multiple tables

Hi everyone,

I'm new to Alteryx and I'm trying to build a simple importation workflow that will read data from several distinct worksheets and save each of them into a different table in the same database.

What I am doing now is to set an Input Data Tool (to read the worksheet) and an Output Data Tool (to insert data into table) for each worksheet and configuring a new database connection for each one (the database is the same, but the tables are different). The problem with this approach is that if I need to change the database connection string, I would need to change every single Output Data tool. So, I wonder if there is a better approach for doing that.

Can anyone help me doing that?

Thanks in advance!

Datasets

In Database

In-Database

Accepted answers

KaneG

It looks like @bsharbo & @michael_treadwell have given you ideas to help you output the data in this fashion. I would like to mention though that you might want to check out setting up an Alias for the connection. You can do this in "Options > Advanced Options > Alias Manager > Standard Connections". That way, if you change the connection string, you only need to change it there.

And then that alias can be referenced in the Input/Output Data tools as such (Sorry about the blotchy boxes for redacted text):

Image 001 - 20160519 - 170547.png

You can also set up a similar process using a workflow constant. Thats what I use for share drives.

Kane

All comments

michael_treadwell

In order for the solution below to work, the data in each worksheet is going to need to be of a similar structure. Same column names, types, etc. If that is not the case, I believe this is still possible but it would require quite a bit more development and testing.

First, you would need to use the dynamic input tool to read all of the input worksheets.

Then, you could set up an input tool inside of a batch macro. Use interface tools to configure the connction string and have the control parameter be the tables to which you need to write.

bsharbo

Can I assume that the reason you need to change the database connection string is that you are first writing to at "DEV" or "UAT" database, and then you want to change the code so that it Points to a "production" database?

If so I have run into your exact question before, and I solved it using the following methodology :-)

#1) Put your input and output connections inside a batch macro.

#2) add one control Parameter to the batch macro. This will be your input to the macro of "database Connection".

#3) Connect an update (action tool) to each of your output tools pointing to the same control Parameter.

#4) Dynamically change the database connection string by replacing the portion that has the Server + database hard-coded in it with whatever you pass into the batch Macro through the action tool.

#5) Make a secondary workflow that calls this batch macro, and passes in the server name + database connection to your control paramater.

By doing this, you could have any number of outputs to the database and the control paramater would then update each one for you, so you would only have to update the control paramater in one location.

If you are doing this as an analytic application there are other ways to solve this problem without using a batch macro (you can simply use a text Box tool for example).

If you need me to post some example code let me know and I can do!

KaneG

And then that alias can be referenced in the Input/Output Data tools as such (Sorry about the blotchy boxes for redacted text):

Image 001 - 20160519 - 170547.png

You can also set up a similar process using a workflow constant. Thats what I use for share drives.

Kane

OceanBass

Hi bsharbo,

can you please give one example using text box to handle above issue- updaing database connection string?

Thanks

Hi2023

I am trying to do something similar but I need to pull 3 different columns from 3 tables (2 are similar and 1 has a column that I need to pull from that is different from the other2) would it still the same way as you listed?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703