Adding Copies to Bibliographic Records

Before bibliographic records can be found in an OPAC search copies will need to be created. It is very important to understand how various tables related to each other in regards to holdings maintenance.

The following procedure will guide you through the process of populating Evergreen with volumes and copies. This is a very simple example. The SQL queries may need to be adjusted for the specific data in your holdings.

  1. Create a staging_items staging table to hold the holdings data:

    CREATE TABLE staging_items (
            l_callnum text, -- call number label
    	hseq int, 
            egid  int,  -- biblio.record_entry_id
            createdate      date,
            l_location        text,
            l_barcode         text,
            l_circ_modifier   text,
            l_owning_lib      text  -- actor.org_unit.shortname
  2. Import the items using the SQL script created using the extract_holdings utility.

    psql -U evergreen -f evergreen

    the file and/or the COPY query may need to be adjusted for your particular circumstances.

  3. Generate shelving locations from your staging table.

    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); 
  4. Generate circulation modifiers from your staging table.

    INSERT INTO config.circ_modifier (code, name, description, sip2_media_type, magnetic_media)
            SELECT  DISTINCT l_circ_modifier AS code,
              l_circ_modifier AS name,
              LOWER(l_circ_modifier) AS description,
              '001' AS sip2_media_type,
              FALSE AS magnetic_media
              FROM  staging_items
              WHERE l_circ_modifier NOT IN (SELECT code FROM config.circ_modifier);
  5. Generate call numbers from your staging table:

    INSERT INTO asset.call_number (creator,editor,record,label,owning_lib)
    SELECT  DISTINCT 1, 1, egid, l.callnum,
    FROM  staging.staging_items l
    JOIN actor.org_unit ou ON (l.owning_lib = ou.shortname);
  6. Generate copies from your staging table:

    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.l_createdate AS create_date,
    	l.l_barcode AS barcode,
    	0 AS STATUS, AS location,
    	2 AS loan_duration,
    	2 AS fine_level,
    	l.l_circ_modifier AS circ_modifier,
    	FALSE AS deposit,
    	WHEN l.l_circ_modifier = 'REFERENCE' THEN TRUE
    	END AS ref,		 AS call_number
    	FROM  staging_items l
    		JOIN actor.org_unit ou
    			ON (l.l_owning_lib = ou.shortname)
    		JOIN asset.copy_location cl
    			ON ( = cl.owning_lib AND l.l_location =
    		JOIN metabib.real_full_rec m 
    			ON (m.record = l.egid)
    		JOIN asset.call_number cn
    			ON ( = cn.owning_lib  
    			AND m.record = cn.record       
    			AND l.l_callnum = cn.label)      

    You should now have copies in your Evergreen database and should be able to search and find the bibliographic records with attached copies.