CODEWITHSUNDEEP
sql
Mohana
Mohana

Reputation: 1

Execute same query for multiple tables in a database

I have 51 tables (in a single DB) having same column and schema for 51 USA states. All i have to do is to run the same query for all 51 tables. The result of each state should return in to new table with a tableformat like this.

Eg. Table name : TX_EED
    New Table name :TX_EED_v0

Following is the query which i will run for each state:

Select distinct a.Geoid, Lat, Long, StateCode, CountyCode, PostalCode,StrVal_SFD ,StrVal_MFD,StrVal_MH from EED2013..TX_EED a
inner join 
(Select Geoid,
SUM(case when LOBNAME ='SFD'THEN CvgAval_MinThresh else 0 end) as StrVal_SFD,
SUM(case when LOBNAME ='MFD'THEN CvgAval_MinThresh else 0 end) as StrVal_MFD,
SUM(case when LOBNAME ='MH'THEN CvgAval_MinThresh else 0 end) as StrVal_MH
FROM EED2013..TX_EED group by Geoid) b
on a.Geoid =b.Geoid-------7,473,869

Upvotes: 0

Views: 743

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you have 51 tables with the same schema, then you should really have a single table. By the way, there are more than "51" state-equivalents in the US -- DC, Puerto Rico, Guam, US Virgin Islands, and various military addresses count as "states" according to the US Post Office (and I think the US Census).

In any case, create a view:

create view v_AllStates as
    select ak.*
    from data_al union all
    select al.*
    from data_al union all
    . . .
    select wy.*
    from data_wy;

You can then use this view in your queries, presumably simplifying your life.

Upvotes: 2

Related Questions