community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

ODBC Connection Multiple Tables

Alteryx Partner

In the Visual Query Builder, is there a way to query more than 1 table at a time?  

For example, in the picture below, I'd like to query all tables with "PEN" in the name. 

Asteroid

Does this need to be an automated process?

 

Also are there multiple tables with the same name?

 

 

And what kind of DB are you querying against?

 

This is probably going to have to happen in the custom query and/or SP tabs.

Alteryx Partner

none of the tables have the same name but they have PEN or SND or something else in common. I want to query against all PEN tables. It's a SQL Server DB.  The schema is identical in all tables. 

Asteroid

Well the more laborious/simpler way is to just union each table together.

 

For an automated solution - sorry I'm not just giving you easy code to do this - you could try:

 

Get tables that contain subtring:

http://sqlhints.com/2014/04/06/how-to-find-all-the-tables-with-name-like-a-given-pattern-in-sql-serv...

 

Loop through the tables and union:

https://stackoverflow.com/questions/12864558/sql-server-while-loop-union-all

 

CREATE TABLE #Temp
(
<COLUMNS>
)

DECLARE @position INT
SET @position = -1

WHILE(@position < 1)
BEGIN

INSERT INTO #Temp (<COLUMNS>)
SELECT * FROM mytable

SET @position = @position + 1

END

SELECT * FROM #Temp

Highlighted

If you look up in the upper right hand corner you will see a Q box.  If you click it you can add a union, except or intersect.   You can also pull another table in by choosing it from the list on the right hand  side of the screen.  

 

Even better use the SQL Editor and enter in a SQL statement with the two tables joined, unioned, etc.

Labels