User Tools

Site Tools


Importing bibliographic holdings via a staging table

In the following example, you would:

  1. Import your bibliographic records into the system as usual
  2. Create the staging_items table as defined below:
    -- Given a staging table with the following structure ...
    CREATE TABLE staging_items (
            callnum text, -- call number label
            bibkey  INT,  -- biblio.record_entry_id
            createdate      DATE,
            location        text,
            barcode         text,
            item_type       text,
            owning_lib      text  -- actor.org_unit.shortname
  3. Insert your holdings into the staging_items table
  4. Then follow the steps starting at … import using the following … below, adjusted to map according to your legacy item types, etc

A complete sample of scripts, instructions, and sample data for importing bib records and holdings can be found on the Evergreen downloads page.

-- ... import using the following ...
-- Wrap this all in a transaction so that if we run
-- into an error, we do not get duplicate values
-- First, we build shelving location
INSERT INTO asset.copy_location (name, owning_lib)
        SELECT  DISTINCT l.location,
          FROM  staging_items l JOIN actor.org_unit ou
                        ON (l.owning_lib = ou.shortname);
-- Needed for Evergreen 1.4 and beyond - will fail with Evergreen 1.2
-- Create circulation modifiers for in-db circulation
-- This is very, very crude but satisfies the FK constraints
INSERT INTO config.circ_modifier (code, name, description, sip2_media_type, magnetic_media)
        SELECT  DISTINCT item_type AS code,
          item_type AS name,
          LOWER(item_type) AS description,
          '001' AS sip2_media_type,
          FALSE AS magnetic_media
          FROM  staging_items
          WHERE item_type NOT IN (SELECT code FROM config.circ_modifier);
-- Import call numbers for bibrecord->library mappings
INSERT INTO asset.call_number (creator,editor,record,label,owning_lib)
        SELECT  DISTINCT 1, 1, l.bibkey , l.callnum,
          FROM  staging_items l
                JOIN biblio.record_entry b ON (l.bibkey =
                JOIN actor.org_unit ou ON (l.owning_lib = ou.shortname);
-- Import base copy data
INSERT INTO asset.copy (
        circ_lib, creator, editor, create_date, barcode,
        STATUS, location, loan_duration,
        fine_level, circ_modifier, deposit, REF, call_number)
        SELECT  DISTINCT AS circ_lib,
                1 AS creator,
                1 AS editor,
                l.createdate AS create_date,
                l.barcode AS barcode,
                0 AS STATUS,
       AS location,
                2 AS loan_duration,
                2 AS fine_level,
                        WHEN l.item_type IN ('REFERENCE', 'DEPOSIT_BK') THEN 'BOOK'
                        ELSE l.item_type
                END AS circ_modifier,
                        WHEN l.item_type = 'DEPOSIT_BK' THEN TRUE
                        ELSE FALSE
                END AS deposit,
                        WHEN l.item_type = 'REFERENCE' THEN TRUE
                        ELSE FALSE
                END AS REF,
       AS call_number
          FROM  staging_items l
                JOIN actor.org_unit ou
                        ON (l.owning_lib = ou.shortname)
                JOIN asset.copy_location cl
                        ON ( = cl.owning_lib AND l.location =
                JOIN asset.call_number cn
                        ON ( = cn.owning_lib AND l.bibkey = cn.record AND l.callnum = cn.label);
-- Commit the transaction if everything went well
importing/holdings/import_via_staging_table.txt · Last modified: 2008/12/01 17:04 by dbs

© 2008-2017 GPLS and others. Evergreen is open source software, freely licensed under GNU GPLv2 or later.
The Evergreen Project is a member of Software Freedom Conservancy.