tr_citystate insert

Dale,

Here is some code I am using to insert that single row.  Hope it helps.

--select * from tr_citystate order by id

USE impresario

-- SET IDENTITY_INSERT to ON.

SET IDENTITY_INSERT tr_citystate on--ON

GO

INSERT INTO tr_citystate (zip5, city, state, country, area_code, usps_code, time_zone, geo_area, inactive,

create_loc, created_by, create_dt, last_updated_by, last_update_dt,id,default_ind ) 

VALUES('77496', 'Sugar Land', 'TX', 1, NULL, 'Sugar Land',

NULL, NULL, NULL, 'Machine Name', NULL,'2012-06-19 00:00:00.000',NULL, NULL,74609,'Y');

SET IDENTITY_INSERT tr_citystate off

GO

Thanks,

Bobby Moseley

601 Preston

Houston, TX 77002

I need to enter a single row into tr_citystate.  How do I get the next id number for this table?

Dale

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

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?