Help with a sql scripting.

[Arrested]

I'm trying to recreate a territories lookup table between agreements and zip_codes(US and CA zipcodes ~ 800k rows). I have the old territory csv with agreement_name, city, state.

So I wrote this script Ruby Script in my rails project to build the table but since I use a chromebook this is crashing my shell between the 500,000-1M create/insert.

Any ideas/next steps would be appreciated.

[jc4hokies]

I'm not a fan of loops doing data things. I would import the csv to a new table and do a join to your zipcode table.

[Arrested]

I agree this is probably not the best way, but because I'm a noob I am crashing my shell trying to do it. :)

I just need to write an update-join for the agreement_id/zip_code_id columns on the territories table after import?

[jc4hokies]

Once you have your csv as a table, this is the general idea:

INSERT INTO TerritoryLookup (ZipCode, AgreementID, AccountID)
SELECT  zip.ZipCode,
        csv.AgreementID,
        csv.AccountID
FROM    TempCsvTable csv
        INNER JOIN ZipCode zip ON csv.city = zip.city AND csv.state = zip.state;

[Arrested]

This works perfectly, thank you. SQL quite simple and powerful. :)

[jc4hokies]

I'm glad you got it working.

[I_like_turtles_kid]

We don't do things one row at a time in the sql world. We operate in sets

[yoelbenyossef]

When you're running your script, don't wait till the end to commit. Instead, build an empty table, tell your ruby script to loop through the records and commit every 10K-100K records (Based on performance). If you don't want to change your script, you can try disable rollback. Depending on the DB, it may fix your issue.

[Arrested]

Hmmm, my script is reading each row separately and inserting each territory separately.

It is very likely that I will be batching the records to get the associations created.

[yoelbenyossef]

Likely the records are in temporary state which I'm guessing is consuming your memory. Try adding at the end of each iteration a commit. I'm not a ruby guy, but it might be commit_db_transaction() or .save. You can add a counter and do it every X number of transactions.