   INSERT INTO NH_ROADS_2009
  (AGENCYID, LOCSYSTEM, ROUTETYPE, ROUTENAME, COUNTY, STARTOFFSET,
  ENDOFFSET, GISID, MAJORROADNAME, DISTRICT, CITY, JURISDICTION,
  AREATYPE, ROADWAYCLASS1, D1NUMTHRULANE, D2NUMTHRULANE, D1AVGLANEWIDTH,
  D2AVGLANEWIDTH, MEDIANTYPE1, MEDIANWIDTH, D1SHOULDERTYPEOUT, D1SHOULDERTYPEIN,
  D2SHOULDERTYPEOUT, D2SHOULDERTYPEIN, D1AVGSHOULDERWIDTHOUT, 
  D1AVGSHOULDERWIDTHIN, D2AVGSHOULDERWIDTHOUT, D2AVGSHOULDERWIDTHIN,
  ACCESSCONTROL, OPERATIONWAY, SEGMENTLENGTH,INTERCHANGEINFLUENCE,
  INCREASINGMILEPOSTS)
  
 select a.unique_id as agencyID, 'A' AS locSystem, 
 case
      when substr(a.sri,1,1)='I'
      then 'I'
      when substr(a.sri,1,1)='U'
      then 'US'
      when substr(a.sri,1,1)='S'
      then 'SR'
      when substr(a.sri,1,1)='T'
      then 'SR'
      when substr(a.sri,1,1)='N'
      then 'SR'
      when substr(a.sri,1,1)='C'
      then 'O'
      when substr(a.sri,1,1)='R'
      then 'O'
      when substr(a.sri,1,1)='L'
      then 'L'
      when substr(a.sri,1,1)='P'
      then 'X'
      when substr(a.sri,1,1)='F'
      then 'X'
  end as routeType,
  a.SRI as routeName, a.county_name as county, a.mp_start as startOffset, a.mp_end as endOffset, 
  a.unique_id as gisID, a.street as majorRoadName, a.spat_join_district as district, a.town_name as city,
 case
      when a.legis_class = 'I'
      then '2'
      when a.legis_class = 'II'
      then '2'
      when a.legis_class = 'III'
      then '2'
      when a.legis_class = 'IV'
      then '4'
      when a.legis_class = 'V'
      then '4'
      when a.legis_class = 'VI'
      then '5'
      when a.legis_class = 'VII'
      then '1'
      else '99' End as jurisdiction,  
 case
      When a.funct_class = '1' THEN 'R'
      When a.funct_class = '2' THEN 'R'
      When a.funct_class = '6' THEN 'R'
      When a.funct_class = '7' THEN 'R'
      When a.funct_class = '8' THEN 'R'
      When a.funct_class = '9' THEN 'R'
      When a.funct_class = '11' THEN 'U'
      When a.funct_class = '12' THEN 'U'
      When a.funct_class = '14' THEN 'U'
      When a.funct_class = '16' THEN 'U'
      When a.funct_class = '17' THEN 'U'
      When a.funct_class = '19' THEN 'U' Else 'X' END as areaType,
 case
      When a.funct_class = '1' THEN '1'
      When a.funct_class = '11' THEN '1'
      When a.funct_class = '2' THEN '2'
      When a.funct_class = '12' THEN '2'
      When a.funct_class = '14' THEN '3'
      When a.funct_class = '6' THEN '4'
      When a.funct_class = '7' THEN '5'
      When a.funct_class = '8' THEN '6'
      When a.funct_class = '9' THEN '7'
      When a.funct_class = '19' THEN '7'
      When a.funct_class = '17' THEN '6'
      When a.funct_class = '16' THEN '4' Else '99' END as roadwayClass1,
 case
      When a.no_lanes = '1' THEN '1'
      When a.no_lanes = '2' THEN '1'
      When a.no_lanes = '3' THEN '1'
      When a.no_lanes = '4' THEN '2'
      When a.no_lanes = '5' THEN '2'
      When a.no_lanes = '6' THEN '3'
      When a.no_lanes = '7' THEN '3'
      When a.no_lanes = '8' THEN '4' 
      WHEN a.no_lanes = '9' THEN '4'
      WHEN a.no_lanes ='10' THEN '5' END AS d1numThruLane,
 case
      When a.no_lanes = '1' THEN '1'
      When a.no_lanes = '2' THEN '1'
      When a.no_lanes = '3' THEN '1'
      When a.no_lanes = '4' THEN '2'
      When a.no_lanes = '5' THEN '2'
      When a.no_lanes = '6' THEN '3'
      When a.no_lanes = '7' THEN '3'
      When a.no_lanes = '8' THEN '4' 
      When a.no_lanes = '9' THEN '4'
      When a.no_lanes = '10' THEN '5' END AS d2numThruLane,
 a.lane_width as d1avgLaneWidth,
 a.lane_width as d2avgLaneWidth,
 case
      When a.median_type = '1' THEN '1'
      When a.median_type = '2' THEN '4'
      When a.median_type = '3' THEN '9' Else '0' END AS medianType1,
 a.median_width as medianWidth,
  case 
      When a.shoulder_type = '1' THEN '98'
      When a.shoulder_type = '2' THEN '1'
      When a.shoulder_type = '3' THEN '3'
      When a.shoulder_type = '4' THEN '99'
      When a.shoulder_type = '5' THEN '4'
      When a.shoulder_type = '6' THen '5' End as d1shoulderTypeOut,
    case 
      When a.shoulder_type = '1' THEN '98'
      When a.shoulder_type = '2' THEN '1'
      When a.shoulder_type = '3' THEN '3'
      When a.shoulder_type = '4' THEN '99'
      When a.shoulder_type = '5' THEN '4'
      When a.shoulder_type = '6' THen '5' End as d1shoulderTypeIn,
    case 
      When a.shoulder_type = '1' THEN '98'
      When a.shoulder_type = '2' THEN '1'
      When a.shoulder_type = '3' THEN '3'
      When a.shoulder_type = '4' THEN '99'
      When a.shoulder_type = '5' THEN '4'
      When a.shoulder_type = '6' THen '5' End as d2shoulderTypeOut, 
  case 
      When a.shoulder_type = '1' THEN '98'
      When a.shoulder_type = '2' THEN '1'
      When a.shoulder_type = '3' THEN '3'
      When a.shoulder_type = '4' THEN '99'
      When a.shoulder_type = '5' THEN '4'
      When a.shoulder_type = '6' THen '5' End as d2shoulderTypeIn,
  a.shldr_right as d1avgShoulderWidthOut,
  a.shldr_left as d1avgShoulderWidthIn,
  a.shldr_right as d2avgShoulderWidthOut,
  a.shldr_left as d2avgShoulderWidthIn,
  case
      When a.access_control = '1' THEN '1'
      When a.access_control = '2' THEN '2' Else '3' End as accessControl,
  case
      When a.facility_type = '1' THEN '1'
      When a.facility_type = '2' THEN '2' 
      Else '99' END as operationWay,
 a.segmentlength as segmentlength,

 a.interchangeinfluence_v2 as interchangeinfluence,
 /*
 decode (a.SRI,	'S0000001A_', 	'NB',
                'S0000001AS', 	'SB',
                'S0000001B_', 	'NB',
                'U0000001__', 	'NB',
                'U0000001_S', 	'SB',
                'U0000001B_', 	'NB',
                'U0000001BS', 	'SB',
                'U0000002__', 	'EB',
                'S0000003A_', 	'NB',
                'S0000003AS', 	'SB',
                'U0000003__', 	'NB',
                'U0000003_S', 	'SB',
                'U0000003B_', 	'NB',
                'S0000004__', 	'EB',
                'S0000004_W', 	'WB',
                'S0000004A_', 	'NB',
                'U0000004__', 	'NB',
                'U0000004_W', 	'WB',
                'S0000009__', 	'EB',
                'S0000009_W', 	'WB',
                'S0000010__', 	'NB',
                'S0000010_S', 	'SB',
                'S0000010A_', 	'NB',
                'S0000011__', 	'EB',
                'S0000011_W', 	'WB',
                'S0000011A_', 	'EB',
                'S0000011B_', 	'NB',
                'S0000011C_', 	'NB',
                'S0000012__', 	'NB',
                'S0000012_S', 	'SB',
                'S0000012A_', 	'NB',
                'S0000013__', 	'NB',
                'S0000016__', 	'NB',
                'S0000016_S', 	'SB',
                'S0000018__', 	'NB',
                'S0000025__', 	'EB',
                'S0000025A_', 	'EB',
                'S0000026__', 	'EB',
                'S0000027__', 	'EB',
                'S0000028__', 	'NB',
                'S0000028_S', 	'SB',
                'S0000028A_', 	'NB',
                'S0000028B_', 	'NB',
                'S0000028BS' ,	'SB',
                'S0000031__', 	'NB',
                'S0000032__', 	'NB',
                'S0000033__', 	'EB',
                'S0000033_W', 	'WB',
                'S0000038__', 	'NB',
                'S0000043__', 	'NB',
                'S0000043_S', 	'SB',
                'S0000049__', 	'NB',
                'S0000063__', 	'NB',
                'S0000075__', 	'EB',
                'S0000077__', 	'EB',
                'S0000078__', 	'NB',
                'S0000085__', 	'NB',
                'S0000087__', 	'EB',
                'S0000088__', 	'EB',
                'I0000089__', 	'NB',
                'I0000089_S', 	'SB',
                'I0000093__', 	'NB',
                'I0000093_S', 	'SB',
                'S0000097__', 	'EB',
                'S0000101__', 	'EB',
                'S0000101_W', 	'WB',
                'S0000101A_', 	'EB',
                'S0000101AW', 	'WB',
                'S0000101E_', 	'EB',
                'S0000101EW', 	'WB',
                'S0000102__', 	'EB',
                'S0000103__', 	'EB',
                'S0000103_W', 	'WB',
                'S0000104__', 	'EB',
                'S0000104_W', 	'WB',
                'S0000106__', 	'NB',
                'S0000106_S', 	'SB',
                'S0000107__', 	'NB',
                'S0000108__', 	'NB',
                'S0000108_S', 	'SB',
                'S0000109__', 	'NB',
                'S0000110__', 	'EB',
                'S0000111__', 	'EB',
                'S0000111_W', 	'WB',
                'S0000111A_', 	'EB',
                'S0000112__', 	'EB',
                'S0000113__', 	'EB',
                'S0000114__', 	'NB',
                'S0000114A_', 	'NB',
                'S0000115__', 	'NB',
                'S0000116__', 	'NB',
                'S0000117__', 	'EB',
                'S0000118__', 	'NB',
                'S0000119__', 	'EB',
                'S0000120__', 	'NB',
                'S0000121__', 	'NB',
                'S0000121A_', 	'NB',
                'S0000122__', 	'NB',
                'S0000123__', 	'NB',
                'S0000124__', 	'EB',
                'S0000125__', 	'NB',
                'S0000126__', 	'NB',
                'S0000127__', 	'NB',
                'S0000128__', 	'NB',
                'S0000130__', 	'EB',
                'S0000130_W', 	'WB',
                'S0000132__', 	'NB',
                'S0000135__', 	'NB',
                'S0000136__', 	'EB',
                'S0000137__', 	'NB',
                'S0000140__', 	'EB',
                'S0000149__', 	'NB',
                'S0000150__', 	'NB',
                'S0000151__', 	'NB',
                'S0000152__', 	'EB',
                'S0000153__', 	'NB',
                'S0000155__', 	'NB',
                'S0000155A_', 	'NB',
                'S0000156__', 	'NB',
                'S0000175__', 	'NB',
                'S0000175A_', 	'EB',
                'S0000202A_', 	'EB',
                'U0000202__', 	'EB',
                'U0000202_W', 	'WB',
                'S0000236__', 	'EB',
                'S0000286__', 	'EB',
                'I0000293__', 	'NB',
                'I0000293_S', 	'SB',
                'U0000302__', 	'EB',
                'I0000393__', 	'EB',
                'I0000393_W', 	'WB',*/
                                'X'/*)*/	as increasingMileposts
 
     
 FROM  roads_2009_midyear_6_8_2010 a 


 WHERE FUNCT_CLASS <> '9'
  AND FUNCT_CLASS <> '19'
  AND FUNCT_CLASS <> '0'
  AND SRI NOT LIKE 'R%'
  AND SRI NOT LIKE 'T%'
  AND SRI NOT LIKE 'C%'
  AND SRI <> 'I0000095__'
  AND SRI <> 'I0000095_S';

