Pass through query selecting multiple tables with same structure ...

[RHorton1976]

Hi there,

Not being very techie myself, I am facing a brainteaser and I was hoping someone could help.

We have a database with about 20 tables with the names in the following format ***_ACNT The first three letters differ examples E01_ACNT N01_ACNT etc

Is it possible to do a pass through query which will select all these tables and return all records and at the same time insert the tables name as a column name?

Thanks for your help.

 

[r937]

SELECT *, 'E01_ACNT' as tablename FROM E01_ACNT
UNION ALL
SELECT *, 'N01_ACNT' FROM N01_ACNT
UNION ALL
...
SELECT *, 'Z01_ACNT' FROM Z01_ACNT


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

You could create a view to do this for you. In a query window (in SQL Server Management Studio)...

Create View All_Acnt
As
Select 'E01' As TableName, Col1, Col2, Col3
From   E01_Acnt

Union All

Select 'N01', Col1, Col2, Col3
From   N01_Acnt

Union All

Select 'xxx', Col1, Col2, Col3
From   xxx_Acnt

In the code above, where you see Col1, Col2, Col3, you will need to replace this with the actual column names from your table. You MUST make sure that each of the individual queries return the same number of columns.

Note that this code will create a view. You only need to do this once. From then on, you can select from the view from anywhere you want.


Ex:

Select * From All_Acnt

or

Select * From All_Acnt Where TableName = 'N01'



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Sorry Rudy. I was still typing.... Getting slow....

My idea is the same as r937 (Rudy). The only real difference is making this a VIEW.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RHorton1976]

Thanks for the proposals. This is useful. Is there anyway though where you can do a search for all the tables with the naming convention of ***_ACNT at the same time?

 

[markros]

Check this thread183-1577077 and look into INFORMATION_SCHEMA.TABLES system view.

 

[RHorton1976]

Thanks for all your help but maybe I'm not making myself clear.... I would like to know whether you can in one passthrough query do a search through the database for all tables containing '_ACNT' in the name and returning the records for all of these tables and at the same time inserting the tablename as a column in order to differentiate between them as they have the same columns. There must be some clever pice of coding for this........and also considering that I am doing this through Access.......is this impossible??

 

[markros]

Did you look up my advice? Also you may want to take a look at sp_MSForEachTable (undocumented in BOL but widely used).

 

[RHorton1976]

Hello Markros - I have thanks but looking at that makes my head spin. I don't even know which are the variables I need to change. Do I paste it into Access? (passthrough query space)

 

[markros]

This unfortunately I don't know - haven't opened Access for years...