• Replies 7 replies
  • Subscribers 338 subscribers
  • Views 109 views
  • Users 0 members are here
Related

tr_citystate insert

Dale Aucoin
$organization

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


Dale

Parents
  • $organization

    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

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Dale Aucoin
    Sent: Tuesday, June 19, 2012 1:18 PM
    To: Moseley, Bobby
    Subject: [Tessitura Technical Forum] tr_citystate insert

     

    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!

  • $organization in reply to Dale Aucoin

    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?

Reply Children
  • $organization in reply to Tom Brown (Past Member)

    Hi Tom,

    We use data from www.zip-codes.com, which is an idea I got from Chris Jensen. I use their CSV format and use an SSIS package to import it into a local table, then I have some simple queries that saves a copy of the current TR_CITYSTATE table (just in case something goes wrong), truncates TR_CITYSTATE, then repopulates it from the table I loaded the CSV file into.

    My plan is to automate it, but since we only do it about quarterly, I haven't done that yet. I'd be happy to share our documentation of the process and code if you're interested.

    Thanks,
    David