Copying/writing data from one table to another

Transact-SQL
1

My Current situation:

I input data into a form, monthly, which writes to a db - this works fine.

DB entries are written to:

State_Mileage
STATE, MONTH, YEAR, MILES

What I am attempting to do is two-fold:
1: Extract data from the State_Mileage table and write it BY STATE to a second table set up as follows:

tbSTATES
STATE, ABBREVIATION, MILES

State and Abbreviation columns are already populated. Miles column is currently all at '0'

My State_Mileage table is currently populated as such (sample/random pull):

STATE MONTH YEAR MILES
STATE MONTH YEAR MILES
ALABAMA 1 2018 598
ARKANSAS 1 2018 281
ARIZONA 1 2018 729
CALIFORNIA 1 2018 574
CONNECTICUT 1 2018 83
DELAWARE 1 2018 15
ALABAMA 2 2018 295
DELAWARE 2 2018 21
GEORGIA 2 2018 14
IOWA 2 2018 370
IDAHO 2 2018 267
ILLINOIS 2 2018 837
ALABAMA 3 2018 311
ARKANSAS 3 2018 223
DELAWARE 3 2018 30
IOWA 3 2018 1259
IDAHO 3 2018 750
ALABAMA 1 2019 709
DELAWARE 1 2019 51
GEORGIA 1 2019 729
IOWA 1 2019 171
ILLINOIS 1 2019 589
INDIANA 1 2019 184
KENTUCKY 1 2019 832
MARYLAND 1 2019 459
MINNESOTA 1 2019 175
MISSISSIPPI 1 2019 221
NORTH CAROLINA 1 2019 1197
NEW JERSEY 1 2019 99
NEW YORK 1 2019 236
OHIO 1 2019 756
PENNSYLVANIA 1 2019 706
SOUTH CAROLINA 1 2019 506
TENNESSEE 1 2019 872
VIRGINIA 1 2019 1195
WISCONSIN 1 2019 334
WEST VIRGINIA 1 2019 431

I am looking to read through the State_Mileage table and populate TOTAL miles for each state into the new column which then can be re-written or overwritten every time I input data into the State_Mileage table. I need to understand how to write data into the State_Mileage table than either run a script or auto-populate the tbSTATE table at the same time.

My entry each month consists of the MONTH, STATE, MILES. Basically I need to append the total STATE miles in the tbSTATE table after entry is completed. All I want is a running total for each state.

The tbSTATE table should result in something similar to below and where the miles would be a grand total of all miles driven at anytime in any state:

STATE ABBREVIATION MILES
Alabama AL 1602
Alaska AK 0
Arizona AZ 729
Arkansas AR 504
California CA 574
Colorado CO 0
2

Something like this:

update a
   set a.miles=b.miles
  from tbstate as a
       inner join (select b.state
                         ,sum(b.miles) as miles
                     from state_milage as b
                    group by b.state
                  ) as b
               on b.state=a.state
;
1 Like
3

With some very minor adjustments, that resolved my issue.
Thank you lots!