tr_citystate insert

The Fort Worth / Dallas area has experienced large growth over the past 10 years.

Shortly after our implementation (about 1 year ago), I visited the usps.com and downloaded their City/Zip matrix.

Using SQL I created a #temporary table using the USPS data, and then compared the zips in that table to the zips in TR_CITYSTATE.

Then, I inserted the “new” zips into TR_CITYSTATE using an INSERT statement.

My SQL skills are weak (at best), but this worked for us …

I’m glad you mentioned this; I need to re-check for any new zips in the metro area.

 

I used something like:

 

Find the differences:

select zip, city

into #zip_diff                                                                                             

from #zip a             -- USPS data

left join #zip1 b       -- TR_CITYSTATE data

on a.zip = b.zip5

where b.zip5 is null    -- this gets the delta

order by zip

 

Then insert the new zips:

 

INSERT INTO TR_CITYSTATE

(    

      zip5,

      city,

      state,

      country,                      -- 1 (US) is the value for all records ,

      area_code,                    -- <you may need to check for current values>

      usps_code,

      time_zone,                   

      geo_area,                    

      inactive,                     -- in our db, 'N' existed for all records

      create_loc,                   -- <insert your value>

      created_by,                   -- <your RAMP id>

      create_dt,                    -- <insert your date>

      last_updated_by,              -- <your RAMP id>

      last_update_dt,               -- insert <insert your date>

--    id,                           -- system will generate this value

      default_ind                   -- insert 'Y'     -- these values do not exist

      )

 

SELECT

      zip [zip5],

      city [city],

      'TX' [state],

      '1'   [country],                               

      ''    [area_code],                             

      city [usps_code],

      '' [time_zone],                                

      '' [geo_area],                                 

      'N' [inactive],                                

      'xxxxx' [create_loc],                    

      'xxxxx'[created_by],                           

      'xxxxx' [create_dt],                     

      '' [last_updated_by],                          

      'xxxxx' [last_update_dt],                

--    id                                             

      'Y'   [default_ind]                            

FROM #ZIP_diff

 

 

Best Regards and Good Luck:

Wendell Baskin

Bass Hall – Fort Worth

wbaskin@basshall.com

 

 

 

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Tom Brown
Sent: Friday, February 07, 2014 14:25
To: Wendell Baskin
Subject: RE: [Tessitura Technical Forum] tr_citystate insert

 

Here at BAM our TR_CITYSTATE has most records update in the year 2000.

However, the USPS changes Zip Codes and official city names from time to time.

Has anyone done a systematic update of this table to match USPS current standards?

If so what was your approach and how did you get the "official" data?

The Fort Worth / Dallas area has experienced large growth over the past 10 years.

Shortly after our implementation (about 1 year ago), I visited the usps.com and downloaded their City/Zip matrix.

Using SQL I created a #temporary table using the USPS data, and then compared the zips in that table to the zips in TR_CITYSTATE.

Then, I inserted the “new” zips into TR_CITYSTATE using an INSERT statement.

My SQL skills are weak (at best), but this worked for us …

I’m glad you mentioned this; I need to re-check for any new zips in the metro area.

 

I used something like:

 

Find the differences:

select zip, city

into #zip_diff                                                                                             

from #zip a             -- USPS data

left join #zip1 b       -- TR_CITYSTATE data

on a.zip = b.zip5

where b.zip5 is null    -- this gets the delta

order by zip

 

Then insert the new zips:

 

INSERT INTO TR_CITYSTATE

(    

      zip5,

      city,

      state,

      country,                      -- 1 (US) is the value for all records ,

      area_code,                    -- <you may need to check for current values>

      usps_code,

      time_zone,                   

      geo_area,                    

      inactive,                     -- in our db, 'N' existed for all records

      create_loc,                   -- <insert your value>

      created_by,                   -- <your RAMP id>

      create_dt,                    -- <insert your date>

      last_updated_by,              -- <your RAMP id>

      last_update_dt,               -- insert <insert your date>

--    id,                           -- system will generate this value

      default_ind                   -- insert 'Y'     -- these values do not exist

      )

 

SELECT

      zip [zip5],

      city [city],

      'TX' [state],

      '1'   [country],                               

      ''    [area_code],                             

      city [usps_code],

      '' [time_zone],                                

      '' [geo_area],                                 

      'N' [inactive],                                

      'xxxxx' [create_loc],                    

      'xxxxx'[created_by],                           

      'xxxxx' [create_dt],                     

      '' [last_updated_by],                          

      'xxxxx' [last_update_dt],                

--    id                                             

      'Y'   [default_ind]                            

FROM #ZIP_diff

 

 

Best Regards and Good Luck:

Wendell Baskin

Bass Hall – Fort Worth

wbaskin@basshall.com

 

 

 

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Tom Brown
Sent: Friday, February 07, 2014 14:25
To: Wendell Baskin
Subject: RE: [Tessitura Technical Forum] tr_citystate insert

 

Here at BAM our TR_CITYSTATE has most records update in the year 2000.

However, the USPS changes Zip Codes and official city names from time to time.

Has anyone done a systematic update of this table to match USPS current standards?

If so what was your approach and how did you get the "official" data?