This section will explain the task of migrating your patron data from comma delimited files into Evergreen. It does not deal with the process of exporting from the non-Evergreen system since this process may vary depending on where you are extracting your patron records. Patron could come from an ILS or it could come from a student database in the case of academic records.

When importing records into Evergreen you will need to populate 3 tables in your Evergreen database:

Before following the procedures below to import patron data into Evergreen, it is a good idea to examine the fields in these tables in order to decide on a strategy for data to include in your import. It is important to understand the data types and constraints on each field.

    UPDATE actor.usr
        SET mailing_address = actor.usr_address.id, billing_address = actor.usr_address.id
        FROM actor.usr_address
        WHERE actor.usr.id = actor.usr_address.usr;

This assumes 1 address per patron. More complex scenarios may require more sophisticated SQL.

The procedure for importing patron can be automated with the help of an sql script. Follow these steps to create an import script:

    BEGIN;

    -- Create staging table.
    CREATE TABLE students (
        student_id int, barcode text, last_name text, first_name text, email text, address_type text,
                street1 text, street2 text, city text, province text, country text, postal_code text, phone
                text, profile int, ident_type int, home_ou int, claims_returned_count int DEFAULT 0, usrname text,
        net_access_level int DEFAULT 2, password text
    );

    --Copy records from your import text file
    COPY students (student_id, last_name, first_name, email, address_type, street1, street2, city, province,
        country, postal_code, phone, password)
        FROM '/home/opensrf/patrons.csv' WITH CSV HEADER;


    --Insert records from the staging table into the actor.usr table.
    INSERT INTO actor.usr (
        profile, usrname, email, passwd, ident_type, ident_value, first_given_name, family_name,
        day_phone, home_ou, claims_returned_count, net_access_level)
        SELECT profile, students.usrname, email, password, ident_type, student_id, first_name,
        last_name, phone, home_ou, claims_returned_count, net_access_level FROM students;

    --Insert records from the staging table into the actor.usr table.
    INSERT INTO actor.card (usr, barcode)
        SELECT actor.usr.id, students.barcode
        FROM students
                INNER JOIN actor.usr
                        ON students.usrname = actor.usr.usrname;

    --Update actor.usr.card field with actor.card.id to associate active card with the user:
    UPDATE actor.usr
        SET card = actor.card.id
        FROM actor.card
        WHERE actor.card.usr = actor.usr.id;

    --INSERT records INTO actor.usr_address from staging table.
    INSERT INTO actor.usr_address (usr, street1, street2, city, state, country, post_code)
        SELECT actor.usr.id, students.street1, students.street2, students.city, students.province,
        students.country, students.postal_code
        FROM students
        INNER JOIN actor.usr ON students.usrname = actor.usr.usrname;


   --Update actor.usr mailing address with id from actor.usr_address table.:
    UPDATE actor.usr
        SET mailing_address = actor.usr_address.id, billing_address = actor.usr_address.id
        FROM actor.usr_address
        WHERE actor.usr.id = actor.usr_address.usr;

    COMMIT;

Placing the sql statements between BEGIN; and COMMIT; creates a transaction block so that if any sql statements fail, the entire process is canceled and the database is rolled back to its original state. Lines beginning with — are comments to let you you what each sql statement is doing and are not processed.

For academic libraries, doing batch updates to add new patrons to the Evergreen database is a critical task. The above procedures and import script can be easily adapted to create an update script for importing new patrons from external databases. If the data import file contains only new patrons, then, the above procedures will work well to insert those patrons. However, if the data load contains all patrons, a second staging table and a procedure to remove existing patrons from that second staging table may be required before importing the new patrons. Moreover, additional steps to update address information and perhaps delete inactive patrons may also be desired depending on the requirements of the institution.

After developing the scripts to import and update patrons have been created, another important task for library staff is to develop an import strategy and schedule which suits the needs of the library. This could be determined by registration dates of your institution in the case of academic libraries. It is important to balance the convenience of patron loads and the cost of processing these loads vs staff adding patrons manually.