WeeklyAlteryxTips#83　Read all tables with the same schema from the database at once.

This blog series is AlteryxTips, a weekly series that delivers Alteryx tips from time to time.

It is common to want to retrieve and use database data with Alteryx Designer, but have you ever needed to load multiple tables with the same schema but different names all at once?

Of course, Alteryx Designer can handle this kind of use case as well.

As sample data, I prepared two tables with the same schema: one named SAMESCHEMA_1e and another named SAMESCHEMA_2e. In this example, I am using Microsoft SQL Server.

As a side note, MS SQL Server is very useful for testing because it allows login using Windows authentication. The free Developer or Express editions can be used for this kind of testing.

The contents of each table are as follows.

Achieving this with SQL

Designer can load data from a database by writing SQL directly. For example, you can use SQL like the following.

SELECT * FROM SAMESCHEMA_1 
UNION 
SELECT * FROM SAMESCHEMA_2

You can simply write this in the SQL Editor tab when selecting a table in the Input Data tool.

As shown below, the data was successfully retrieved.

However, in this case, you cannot dynamically change the table names as is.

Use a Batch macro

To be honest, this is the main recommended approach.

This method involves preparing a table list and rewriting the SQL using a batch macro. If the table names are listed, it is also easy to narrow the target tables in various ways with the Filter tool, making this a flexible approach. Alternatively, most database management systems have system tables that store table lists, so you can read from those system tables to make the process even more dynamic.

I created a simple batch macro like this.

The Action tool settings are as follows.

Since I want to specify the table name this time, select “Table – value.”

You can create it in this simple workflow.

A workflow that obtains the table list from Microsoft SQL Server’s INFORMATION_SCHEMA system table, identifies the relevant tables, and loads them would look like the following.

In the leftmost Input Data tool, connect to the INFORMATION_SCHEMA table, use the Filter tool to extract the tables that begin with SAMESCHEMA and end with “e”, and then pass the table names into the batch macro.

This is the general approach. It can be used dynamically with various databases.

With BigQuery, this can be handled easily using SQL

When Google BigQuery is linked with GA4, the data is exported to a different table every day. For example, the table name might look like this:

analytics_[YOUR_GA4_ID].events_[YYYYMMDD]

In this case, if you want to extract all data for October 2024 at once, for example, you can use SQL like the following.

select * from `analytics_1234567890.events_202410*`

The GA4 ID part is set to “1234567890” here as a placeholder, but in practice you should match it to the actual dataset name that has been created.

In the case of BigQuery, you can use wildcards in table names. However, you need to be careful because loading a large amount of data at once will directly impact costs, so try to narrow the scope as much as possible. Using “*” to specify a column will load all columns, so it’s recommended to use this only when necessary. The same applies to table names; attempting to load data for the entire period at once can be extremely costly, so please be aware of this.

Conclusion

• This article introduced how to load multiple tables with the same schema from a database into Alteryx all at once.
• You can write SQL directly, but a batch macro provides a more versatile way to handle this.
• In Google BigQuery, wildcards can be specified in table names.

Sample Workflow Download

The Next post will be…

I would like to try using Ask Alteryx. Actually, I wrote the article “I try to use Alteryx Copilot” in Japanese in Feb, 2025. But now, it was changed to “Ask Alteryx.” And also, AI capabilities evolve so quickly that I’m also curious to see how things have changed now, more than a year later.