importing:holdings:import_via_staging_table
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
importing:holdings:import_via_staging_table [2008/02/27 11:59] – Mention item type mapping requirement during holdings import dbs | importing:holdings:import_via_staging_table [2022/02/10 13:34] (current) – external edit 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ======Importing bibliographic holdings via a staging table====== | ||
+ | In the following example, you would: | ||
+ | |||
+ | - Import your bibliographic records into the system [[http:// | ||
+ | - Create the '' | ||
+ | -- Given a staging table with the following structure ... | ||
+ | |||
+ | CREATE TABLE staging_items ( | ||
+ | callnum text, -- call number label | ||
+ | bibkey | ||
+ | createdate | ||
+ | location | ||
+ | barcode | ||
+ | item_type | ||
+ | owning_lib | ||
+ | ); | ||
+ | </ | ||
+ | - Insert your holdings into the '' | ||
+ | - Then follow the steps starting at '' | ||
+ | |||
+ | A complete sample of scripts, instructions, | ||
+ | |||
+ | <code sql> | ||
+ | -- ... import using the following ... | ||
+ | |||
+ | -- Wrap this all in a transaction so that if we run | ||
+ | -- into an error, we do not get duplicate values | ||
+ | BEGIN; | ||
+ | |||
+ | -- First, we build shelving location | ||
+ | INSERT INTO asset.copy_location (name, owning_lib) | ||
+ | SELECT | ||
+ | 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, | ||
+ | SELECT | ||
+ | item_type AS name, | ||
+ | LOWER(item_type) AS description, | ||
+ | ' | ||
+ | FALSE AS magnetic_media | ||
+ | FROM staging_items | ||
+ | WHERE item_type NOT IN (SELECT code FROM config.circ_modifier); | ||
+ | |||
+ | -- Import call numbers for bibrecord-> | ||
+ | INSERT INTO asset.call_number (creator, | ||
+ | SELECT | ||
+ | FROM staging_items l | ||
+ | JOIN biblio.record_entry b ON (l.bibkey = b.id) | ||
+ | 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, | ||
+ | status, location, loan_duration, | ||
+ | fine_level, circ_modifier, | ||
+ | SELECT | ||
+ | 1 AS creator, | ||
+ | 1 AS editor, | ||
+ | l.createdate AS create_date, | ||
+ | l.barcode AS barcode, | ||
+ | 0 AS status, | ||
+ | cl.id AS location, | ||
+ | 2 AS loan_duration, | ||
+ | 2 AS fine_level, | ||
+ | CASE | ||
+ | WHEN l.item_type IN (' | ||
+ | ELSE l.item_type | ||
+ | END AS circ_modifier, | ||
+ | CASE | ||
+ | WHEN l.item_type = ' | ||
+ | ELSE FALSE | ||
+ | END AS deposit, | ||
+ | CASE | ||
+ | WHEN l.item_type = ' | ||
+ | ELSE FALSE | ||
+ | END AS ref, | ||
+ | cn.id AS call_number | ||
+ | FROM staging_items l | ||
+ | JOIN actor.org_unit ou | ||
+ | ON (l.owning_lib = ou.shortname) | ||
+ | JOIN asset.copy_location cl | ||
+ | ON (ou.id = cl.owning_lib AND l.location = cl.name) | ||
+ | JOIN asset.call_number cn | ||
+ | ON (ou.id = cn.owning_lib AND l.bibkey = cn.record AND l.callnum = cn.label); | ||
+ | |||
+ | -- Commit the transaction if everything went well | ||
+ | COMMIT; | ||
+ | </ |