ODBC Connection Multiple Tables

[bb213]

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. 

[WillBowman]

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.

[bb213]

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. 

[WillBowman]

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

[jeep32mudtaman]

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.