CineFiles Data Mapping

SELECT
   name_id,
   ( convert( varchar(15), names.name_id), '') as shortIdentifier,
   ISNULL( fname, '' ) as foreName,
   ISNULL( mname, '' ) as middleName,
   lname as surName,
   ISNULL( gen_suffix,'' ) as nameAddition,

   fname + substring(' ', sign(datalength(fname)), 1) +
   mname + substring(' ', sign(datalength(mname)), 1) +
   lname + substring(' ', sign(datalength(gen_suffix)), 1) +
   gen_suffix as displayName,

   ISNULL(convert( char(4), datepart( year, dob )), '' ) as birthYear,
   dob as earliestScalarBirthDate,
   dateadd( second, -1, dateadd( year, 1, dob )) as latestScalarBirthDate,

   ISNULL(convert( char(4), datepart( year, dod )), '' ) as deathYear,
   dod as earliestScalarDeathDate,
   dateadd( second, -1, dateadd( year, 1, dod )) as latestScalarDeathhDate,

   ISNULL( city, '' ) as city,
   ISNULL( state, '' ) as state,
   cntry_id,
   ISNULL( biog, '' ) as bioNote,
   name_type,
   ISNULL( convert( char(1), code ), '' ) as accessCode,
   entered,
   modified,
   ISNULL( entered_by, '' ) as enteredBy,
   ISNULL( modified_by, '' ) as modifiedBy,
   ISNULL( verified_by, '' ) as verifiedBy,
   ISNULL( src_id, '' ) as authSource,
   ISNULL( note, '' ) as nameNote,
   ISNULL( url, '' ) as URL

FROM
   names

WHERE
   name_type = 1
   AND
   name_id > 1

SELECT
   name_id,
   0 as altname_id,
   ISNULL( convert( varchar( 15 ), name_id ), '' ) as shortIdentifier,
   ISNULL( fname, '' ) as foreName,
   ISNULL( mname, '' ) as middleName,
   lname as surName,
   ISNULL( gen_suffix, '' ) as nameAddition,
   fname + substring( ' ', sign( datalength( fname ) ), 1 ) +
   mname + substring( ' ', sign( datalength( mname ) ), 1 ) +
   lname + substring( ' ', sign( datalength( gen_suffix ) ), 1 ) +
   gen_suffix as displayName,
   '' as srcID,
   '' as  nameNote
FROM
   names

WHERE
   name_type = 1
   AND
   name_id > 1

UNION

SELECT
   a.name_id,
   a.altname_id,
   ISNULL( convert( varchar( 15 ), a.name_id ), '' ) as shortIdentifier,
   ISNULL( a.fname, '' ) as foreName,
   ISNULL( a.mname, '' ) as middleName,
   a.lname as surName,
   ISNULL( a.gen_suffix, '' ) as nameAddition,
   a.fname + substring( ' ', sign( datalength( a.fname ) ), 1 ) +
   a.mname + substring( ' ', sign( datalength( a.mname ) ), 1 ) +
   a.lname + substring( ' ', sign( datalength( a.gen_suffix ) ), 1 ) +
   a.gen_suffix as displayName,
   ISNULL( a.src_id, '' ) as srcID,
   ISNULL( a.note, '' ) as nameNote

FROM altnames a, names n
     HAVING ( n.name_type = 1
        AND  n.name_id = a.name_id
        AND  n.name_id > 1  )

ORDER BY name_id, altname_id

SELECT
   name_id,
   ISNULL( convert( varchar(15), names.name_id), '') as shortIdentifier,
   lname as displayName,
   ISNULL( city, '' ) as foundingCity,
   ISNULL( state, '' ) as foundingState,
   cntry_id,

   ISNULL( convert( char(4), datepart( year, dob )), '' ) as foundingYear,

   ISNULL( dob, NULL ) as earliestScalarBirthDate,
   dateadd( second, -1, dateadd( year, 1, dob )) as latestScalarBirthDate,

   ISNULL( biog, '' ) as bioNote,
   ISNULL( note, '' ) as nameNote,
   ISNULL( src_id, '' ) as authSourceID,

   ISNULL( convert( char(1), code ), '' ) as accessCode,
   ISNULL( url, '' ) as URL,

   entered,
   modified,
   ISNULL( entered_by, '' ) as enteredBy,
   ISNULL( modified_by, '' ) as modifiedBy

FROM
   names

WHERE
   name_type = 2 AND name_id > 1

ORDER BY name_id

SELECT name_id, lname, name_type, code, src_id, note,
entered, entered_by, modified, modified_by
FROM names
where name_type=3
order by lname

SELECT
   name_id,
   0 as altname_id,
   ISNULL( convert( varchar( 15 ), name_id ), '' ) as shortIdentifier,
   ISNULL( fname, '' ) as foreName,
   ISNULL( mname, '' ) as middleName,
   lname as surName,
   ISNULL( gen_suffix, '' ) as nameAddition,
   fname + substring( ' ', sign( datalength( fname ) ), 1 ) +
   mname + substring( ' ', sign( datalength( mname ) ), 1 ) +
   lname + substring( ' ', sign( datalength( gen_suffix ) ), 1 ) +
   gen_suffix as displayName,
   '' as srcID,
   '' as nameNote

FROM
   names

WHERE
   name_type = 1 AND name_id > 1

UNION

SELECT
   a.name_id,
   a.altname_id,
   ISNULL( convert( varchar( 15 ), a.name_id ), '' ) as shortIdentifier,
   ISNULL( a.fname, '' ) as foreName,
   ISNULL( a.mname, '' ) as middleName,
   a.lname as surName,
   ISNULL( a.gen_suffix, '' ) as nameAddition,
   a.fname + substring( ' ', sign( datalength( a.fname ) ), 1 ) +
   a.mname + substring( ' ', sign( datalength( a.mname ) ), 1 ) +
   a.lname + substring( ' ', sign( datalength( a.gen_suffix ) ), 1 ) +
   a.gen_suffix as displayName,
   ISNULL( a.src_id, '' ) as srcID,
   ISNULL( a.note, '' ) as nameNote

FROM
   altnames a, names n

HAVING ( n.name_type = 1
   AND n.name_id = a.name_id
   AND n.name_id > 1 )

ORDER BY
   name_id, altname_id

SELECT cm.name_id committeeId, rtrim(cm.src_id) src_id,
   p.fname + substring(' ', sign(datalength(p.fname)), 1) +
   p.mname + substring(' ', sign(datalength(p.mname)), 1) +
   p.lname + substring(' ', sign(datalength(p.gen_suffix)), 1) +
   p.gen_suffix as member_displayName,
   ISNULL( convert( varchar(15), p.name_id), '') as member_shortIdentifier
FROM committeemembers cm, names c, names p
where cm.name_id=c.name_id and cm.member_id=p.name_id
and c.name_type=3 and cm.member_id<>1
order by c.name_id, member_displayName

...

SELECT g.genre_id,
   'genre'||convert(varchar, genre_id) shortid,
   g.genre,
   rtrim(src_id) authsource,
   rtrim(note) note
FROM genres g
where genre_id <> 1
order by genre

SELECT  subj_id,
        'subject'||convert(varchar, subj_id) shortid,
        subj,
        rtrim(src_id),
        note
FROM subjects
where subj_id <> 1
order by subje

SELECT g.genre_id,
   'genre'||convert(varchar, genre_id) shortid,
   g.genre,
   rtrim(src_id) authsource,
   rtrim(note) note,
   0 sortid
FROM genres g
where genre_id <> 1
union
select gl.genre_id,
      'genre'||convert(varchar, genre_id) shortid,
      gl.genre_term,
      null authsource,
      null note,
      gl.altgenre_id sortid
from genres_list gl
where altgenre_id <> 0
order by genre_id, sortid

select
    f.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), f.film_id) as short_identifier,
    substring(
        substring(
            f.prefix + ' ', 1,
            datalength( f.prefix + ' ' ) -
            sign( patindex( '%[''-]', f.prefix ))),
            sign( patindex( '%[^^I ]%', f.prefix )), 10 ) + f.title as display_name,
    'urn:cspace:cinefiles.cspace.berkeley.edu:vocabularies:name(worktype):item:name(film)''Film''' as work_type,
    f.note as history_notes,
    f.entered as created_at,
    case
        when f.entered_by = 'lt' then 'Linda Tadic'
        else
            eb.fname + substring( ' ', sign( datalength( eb.fname ) ), 1 )
            + eb.mname + substring( ' ', sign( datalength( eb.mname ) ), 1 )
            + eb.lname
    end as created_by,
    f.modified as updated_at,
    case
        when f.modified_by = 'lt' then 'Linda Tadic'
        else
            mb.fname + substring( ' ', sign( datalength( mb.fname ) ), 1 )
            + mb.mname + substring( ' ', sign( datalength( mb.mname ) ), 1 )
            + mb.lname
    end as updated_by,
    'urn:cspace:cinefiles.cspace.berkeley.edu:workauthorities:name(work):item:name(pfafilm'
        + convert(varchar(10), f.film_id) + ')'''
        + substring(
        substring(
            f.prefix + ' ', 1,
            datalength( f.prefix + ' ' ) -
            sign( patindex( '%[''-]', f.prefix ))),
            sign( patindex( '%[^^I ]%', f.prefix )), 10 ) + f.title
        + '''' as refname
from films f, pfastaff eb, pfastaff mb
where f.entered_by *= eb.initials
and f.modified_by *= mb.initials
and f.film_id > 1
order by legacy_film_id;

-- film term names
-- from original titles from films table
-- union English titles from films table
-- union alternate titles from alttitles table
-- sort order: original, English, alternate
select
    f.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), f.film_id) as short_identifier,
    case when f.prefix is null or f.prefix = '' then f.title
        else substring(
            substring(
                f.prefix + ' ', 1,
                datalength( f.prefix + ' ' ) -
                sign( patindex( '%[''-]', f.prefix ))),
                sign( patindex( '%[^^I ]%', f.prefix )), 10 ) + f.title
    end as term_display_name,
    ltrim(rtrim(f.title)) as term_name,
    ltrim(rtrim(f.prefix)) as term_qualifier,
    'Original Title' as term_type,
    'complete' as term_status,
    null as term_language,
    1 as preferred_flag,
    1 as pos,
    ltrim(rtrim(f.src_id)) as term_source_id,
    'urn:cspace:cinefiles.cspace.berkeley.edu:citationauthorities:name(citation):item:name('
        + f.src_id + ')''' + a.source + '''' as term_source_refname,
    null as term_source_note
from
    films f,
    authsources a
where f.src_id *= a.src_id
and f.film_id > 1
union
select
    f.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), f.film_id) as short_identifier,
    case when f.eng_prefix is null or f.eng_prefix = '' then f.eng_title
        else substring(
            substring(
                f.eng_prefix + ' ', 1,
                datalength( f.eng_prefix + ' ' ) -
                sign( patindex( '%[''-]', f.eng_prefix ))),
                sign( patindex( '%[^^I ]%', f.eng_prefix )), 10 ) + f.eng_title
    end as term_display_name,
    ltrim(rtrim(f.eng_title)) as term_name,
    ltrim(rtrim(f.eng_prefix)) as term_qualifier,
    'English Title' as term_type,
    'complete' as term_status,
    'urn:cspace:cinefiles.cspace.berkeley.edu:vocabularies:name(languages):item:name(ENG)''English''' as term_language,
    0 as preferred_flag,
    2 as pos,
    ltrim(rtrim(f.src_id)) as term_source_id,
    'urn:cspace:cinefiles.cspace.berkeley.edu:citationauthorities:name(citation):item:name('
        + f.src_id + ')''' + a.source + '''' as term_source_refname,
    null as term_source_note
from
    films f,
    authsources a
where f.src_id *= a.src_id
and f.eng_title is not null
and f.eng_title != ''
and f.film_id > 1
union
select
    a.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), a.film_id) as short_identifier,
    case when a.prefix is null or a.prefix = '' then a.title
        else substring(
            substring(
                a.prefix + ' ', 1,
                datalength( a.prefix + ' ' ) -
                sign( patindex( '%[''-]', a.prefix ))),
                sign( patindex( '%[^^I ]%', a.prefix )), 10 ) + a.title
    end as term_display_name,
    ltrim(rtrim(a.title)) as term_name,
    ltrim(rtrim(a.prefix)) as term_qualifier,
    'Alternate Title' as term_type,
    'complete' as term_status,
    null as term_language,
    0 as preferred_flag,
    3 as pos,
    ltrim(rtrim(a.src_id)) as term_source_id,
    'urn:cspace:cinefiles.cspace.berkeley.edu:citationauthorities:name(citation):item:name('
        + a.src_id + ')''' + auth.source + '''' as term_source_refname,
    a.note as term_source_note
from
    alttitles a,
    authsources auth
where a.src_id *= auth.src_id
and a.title is not null
and a.title != ''
and a.film_id > 1
and a.alttitle_id not in (
    select alt.alttitle_id from alttitles alt, films f
    where substring(
        substring(
            alt.prefix + ' ', 1,
            datalength( alt.prefix + ' ' ) -
            sign( patindex( '%[''-]', alt.prefix ))),
            sign( patindex( '%[^^I ]%', alt.prefix )), 10 ) + alt.title
    = substring(
        substring(
            f.prefix + ' ', 1,
            datalength( f.prefix + ' ' ) -
            sign( patindex( '%[''-]', f.prefix ))),
            sign( patindex( '%[^^I ]%', f.prefix )), 10 ) + f.title
    and alt.film_id = f.film_id)
and a.alttitle_id not in (
    select alt.alttitle_id from alttitles alt, films f
    where substring(
        substring(
            alt.prefix + ' ', 1,
            datalength( alt.prefix + ' ' ) -
            sign( patindex( '%[''-]', alt.prefix ))),
            sign( patindex( '%[^^I ]%', alt.prefix )), 10 ) + alt.title
    = substring(
        substring(
            f.eng_prefix + ' ', 1,
            datalength( f.eng_prefix + ' ' ) -
            sign( patindex( '%[''-]', f.eng_prefix ))),
            sign( patindex( '%[^^I ]%', f.eng_prefix )), 10 ) + f.eng_title
    and alt.film_id = f.film_id)
order by legacy_film_id, pos;

select
    f.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), f.film_id) as short_identifier,
    f.name_id as legacy_name_id,
    ltrim(rtrim(case
        when n.name_type = 1 then
            n.fname + substring(' ', sign(datalength(n.fname)), 1) +
            n.mname + substring(' ', sign(datalength(n.mname)), 1) +
            n.lname + substring(' ', sign(datalength(n.gen_suffix)), 1) + n.gen_suffix
        when n.name_type = 2 then n.lname
        when n.name_type = 3 then n.lname
    end)) as creator_display_name,
    'urn:cspace:cinefiles.cspace.berkeley.edu:vocabularies:name(workcreatortype):item:name(director)''Director''' as creator_type,
    case
        when n.name_type = 1 then
            'urn:cspace:cinefiles.cspace.berkeley.edu:personauthorities:name(person):item:name(person'
            + convert(varchar(10), n.name_id) + ')'''
            + ltrim(rtrim(n.fname + substring(' ', sign(datalength(n.fname)), 1)
                + n.mname + substring(' ', sign(datalength(n.mname)), 1)
                + n.lname + substring(' ', sign(datalength(n.gen_suffix)), 1) + n.gen_suffix))
            + ''''
        when n.name_type = 2 then
            'urn:cspace:cinefiles.cspace.berkeley.edu:orgauthorities:name(committee):item:name(committee'
            + convert(varchar(10), n.name_id) + ')'''
            + ltrim(rtrim(n.lname)) + ''''
        when n.name_type = 3 then
            'urn:cspace:cinefiles.cspace.berkeley.edu:orgauthorities:name(corporation):item:name(organization'
            + convert(varchar(10), n.name_id) + ')'''
            + ltrim(rtrim(n.lname)) + ''''
    end as creator_refname
from
    films f,
    names n
where f.name_id *= n.name_id
and f.film_id > 1
and f.name_id > 1
union
select
    fd.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), fd.film_id) as short_identifier,
    fd.name_id as legacy_name_id,
    ltrim(rtrim(case
        when n.name_type = 1 then
            n.fname + substring(' ', sign(datalength(n.fname)), 1) +
            n.mname + substring(' ', sign(datalength(n.mname)), 1) +
            n.lname + substring(' ', sign(datalength(n.gen_suffix)), 1) + n.gen_suffix
        when n.name_type = 2 then n.lname
        when n.name_type = 3 then n.lname
    end)) as creator_display_name,
    'urn:cspace:cinefiles.cspace.berkeley.edu:vocabularies:name(workcreatortype):item:name(director)''Director''' as creator_type,
    case
        when n.name_type = 1 then
            'urn:cspace:cinefiles.cspace.berkeley.edu:personauthorities:name(person):item:name(person'
            + convert(varchar(10), n.name_id) + ')'''
            + ltrim(rtrim(n.fname + substring(' ', sign(datalength(n.fname)), 1)
                + n.mname + substring(' ', sign(datalength(n.mname)), 1)
                + n.lname + substring(' ', sign(datalength(n.gen_suffix)), 1) + n.gen_suffix))
            + ''''
        when n.name_type = 2 then
            'urn:cspace:cinefiles.cspace.berkeley.edu:orgauthorities:name(committee):item:name(committee'
            + convert(varchar(10), n.name_id) + ')'''
            + ltrim(rtrim(n.lname)) + ''''
        when n.name_type = 3 then
            'urn:cspace:cinefiles.cspace.berkeley.edu:orgauthorities:name(corporation):item:name(organization'
            + convert(varchar(10), n.name_id) + ')'''
            + ltrim(rtrim(n.lname)) + ''''
    end as creator_refname
from
    filmdirectors fd,
    names n
where fd.name_id *= n.name_id
and fd.film_id > 1
and fd.name_id > 1
order by legacy_film_id, creator_display_name;

select distinct
    fp.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), fp.film_id) as short_identifier,
    fp.name_id as legacy_name_id,
    ltrim(rtrim(case
        when n.name_type = 1 then
            n.fname + substring(' ', sign(datalength(n.fname)), 1) +
            n.mname + substring(' ', sign(datalength(n.mname)), 1) +
            n.lname + substring(' ', sign(datalength(n.gen_suffix)), 1) + n.gen_suffix
        when n.name_type = 2 then n.lname
        when n.name_type = 3 then n.lname
    end)) as publisher_display_name,
    'urn:cspace:cinefiles.cspace.berkeley.edu:vocabularies:name(workpublishertype):item:name(productioncompany)''Production Company''' as publisher_type,
    case
        when n.name_type = 1 then
            'urn:cspace:cinefiles.cspace.berkeley.edu:personauthorities:name(person):item:name(person'
            + convert(varchar(10), n.name_id) + ')'''
            + ltrim(rtrim(n.fname + substring(' ', sign(datalength(n.fname)), 1)
                + n.mname + substring(' ', sign(datalength(n.mname)), 1)
                + n.lname + substring(' ', sign(datalength(n.gen_suffix)), 1) + n.gen_suffix))
            + ''''
        when n.name_type = 2 then
            'urn:cspace:cinefiles.cspace.berkeley.edu:orgauthorities:name(committee):item:name(committee'
            + convert(varchar(10), n.name_id) + ')'''
            + ltrim(rtrim(n.lname)) + ''''
        when n.name_type = 3 then
            'urn:cspace:cinefiles.cspace.berkeley.edu:orgauthorities:name(corporation):item:name(organization'
            + convert(varchar(10), n.name_id) + ')'''
            + ltrim(rtrim(n.lname)) + ''''
    end as publisher_refname
from
    filmprodcos fp,
    names n
where fp.name_id *= n.name_id
and fp.film_id > 1
and fp.name_id > 1
order by legacy_film_id, publisher_display_name;

select
    f.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), f.film_id) as short_identifier,
    f.year as display_date,
    convert(varchar(4), f.year) + '-01-01T00:00:00Z' as earliest_scalar,
    f.year as earliest_year,
    1 as earliest_month,
    1 as earliest_day,
    convert(varchar(4), f.year)  + '-12-31T23:59:59Z' as latest_scalar,
    f.year as latest_year,
    12 as latest_month,
    31 as latest_day
from
    films f
where f.film_id > 1
and f.year is not null
union
select
    fy.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), fy.film_id) as short_identifier,
    fy.year as display_date,
    convert(varchar(4), fy.year) + '-01-01T00:00:00Z' as earliest_scalar,
    fy.year as earliest_year,
    1 as earliest_month,
    1 as earliest_day,
    convert(varchar(4), fy.year)  + '-12-31T23:59:59Z' as latest_scalar,
    fy.year as latest_year,
    12 as latest_month,
    31 as latest_day
from
    filmyears fy
where fy.film_id > 1
and fy.year is not null
order by legacy_film_id, display_date;

select
    f.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), f.film_id) as short_identifier,
    f.lang_id as legacy_lang_id,
    l.lang_tla as language_code,
    ltrim(rtrim(l.lang)) as language_name,
    'urn:cspace:cinefiles.cspace.berkeley.edu:vocabularies:name(languages):item:name(' +
        l.lang_tla + ')''' + ltrim(rtrim(l.lang)) + '''' as language_refname
from
    films f,
    languages l
where f.lang_id = l.lang_id
and f.film_id > 1
and l.lang_id > 1
union
select
    fl.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), fl.film_id) as short_identifier,
    fl.lang_id as legacy_lang_id,
    l.lang_tla as language_code,
    ltrim(rtrim(l.lang)) as language_name,
    'urn:cspace:cinefiles.cspace.berkeley.edu:vocabularies:name(languages):item:name(' +
        l.lang_tla + ')''' + ltrim(rtrim(l.lang)) + '''' as language_refname
from
    filmlangs fl,
    languages l
where fl.lang_id = l.lang_id
and fl.film_id > 1
and l.lang_id > 1
order by legacy_film_id, language_name;

select
    f.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), f.film_id) as short_identifier,
    f.cntry_id as legacy_country_id,
    c.cntry_code as country_code,
    ltrim(rtrim(c.country)) as country_name,
    'urn:cspace:cinefiles.cspace.berkeley.edu:vocabularies:name(country):item:name(' +
        ltrim(rtrim(c.cntry_code)) + ')''' + ltrim(rtrim(c.country)) + '''' as country_refname
from
    films f,
    countries c
where f.cntry_id = c.cntry_id
and f.film_id > 1
and c.cntry_id > 1
union
select
    fc.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), fc.film_id) as short_identifier,
    fc.cntry_id as legacy_country_id,
    c.cntry_code as country_code,
     ltrim(rtrim(c.country)) as country_name,
    'urn:cspace:cinefiles.cspace.berkeley.edu:vocabularies:name(country):item:name(' +
        ltrim(rtrim(c.cntry_code)) + ')''' + ltrim(rtrim(c.country)) + '''' as country_refname
from
    filmcountries fc,
    countries c
where fc.cntry_id = c.cntry_id
and fc.film_id > 1
and c.cntry_id > 1
order by legacy_film_id, country_name;

select
    fg.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), fg.film_id) as short_identifier,
    fg.genre_id as legacy_genre_id,
    ltrim(rtrim(g.genre)) as genre_name,
    'urn:cspace:cinefiles.cspace.berkeley.edu:conceptauthorities:name(genre):item:name(genre'
        + convert(varchar(4), fg.genre_id) + ')'''
        + ltrim(rtrim(g.genre))
        + '''' as genre_refname
from
    filmgenres fg,
    genres g
where fg.genre_id = g.genre_id
and fg.film_id > 1
and g.genre_id > 1
order by legacy_film_id, genre_name;

select
    fs.film_id as legacy_film_id,
    'pfafilm' + convert(varchar(10), fs.film_id) as short_identifier,
    fs.subj_id as legacy_subj_id,
    ltrim(rtrim(s.subj)) as subject_name,
    'urn:cspace:cinefiles.cspace.berkeley.edu:conceptauthorities:name(subject):item:name(subject'
        + convert(varchar(6), fs.subj_id) + ')'''
        + ltrim(rtrim(s.subj))
        + '''' as subj_refname
from
    filmsubjs fs,
    subjects s
where fs.subj_id = s.subj_id
and fs.film_id > 1
and s.subj_id > 1
order by legacy_film_id, subject_name;

SELECT
  d.doc_id,
  title,
  rtrim(prefix) prefix,
  case when (prefix like '%''' or prefix like '%-' or prefix is null) then prefix||title
       else prefix||' '||title end as docDisplayName,
  type_id,
  pages numberObjects,
  pg_info pagination,
  case when d.src_id <> 1 then d.src_id end as srcShortId,
  case when d.src_id <> 1 then n.name_type end as srcNameType,
  n.fname + substring( ' ', sign( datalength( n.fname ) ), 1 ) +
    n.mname + substring( ' ', sign( datalength( n.mname ) ), 1 ) +
    n.lname + substring( ' ', sign( datalength( n.gen_suffix ) ), 1 ) +
    n.gen_suffix as srcDisplayName,
  date_string,
  early_date,
  late_date,
  case when (early_date>2415021 and early_date<2456602) then dateadd(dd,(early_date - 2415021),
    convert(date, '1900-01-01', 103))
    end as begin_date_scalar,
  case when (early_date>2415021 and early_date<2456602) then datepart(year, dateadd(dd,
 (early_date - 2415021),
    convert(datetime, '1900-01-01', 103)))
    end as begin_date_year,
  case when (early_date>2415021 and early_date<2456602) then datepart(month, dateadd(dd,
 (early_date - 2415021),
    convert(datetime, '1900-01-01', 103)))
    end as begin_date_month,
  case when (early_date>2415021 and early_date<2456602) then datepart(day, dateadd(dd,
 (early_date - 2415021),
    convert(datetime, '1900-01-01', 103)))
    end as begin_date_day,
  case when (late_date>2415021 and late_date<2456602) then dateadd(dd,(late_date - 2415021),
    convert(date, '1900-01-01', 103))
    end as end_date_scalar,
  case when (late_date>2415021 and late_date<2456602 and early_date<>late_date) then datepart(year,
  dateadd(dd,(late_date - 2415021),
    convert(datetime, '1900-01-01', 103)))
    end as end_date_year,
  case when (late_date>2415021 and late_date<2456602 and early_date<>late_date) then
 datepart(month, dateadd(dd,(late_date - 2415021),
    convert(datetime, '1900-01-01', 103)))
    end as end_date_month,
  case when (late_date>2415021 and late_date<2456602 and early_date<>late_date) then datepart(day,
  dateadd(dd,(late_date - 2415021),
    convert(datetime, '1900-01-01', 103)))
    end as end_date_day,
  sort_date,
  d.code,
  d.note briefDescription,
  case when cast_cr=1 then 'true' else 'false' end as cast_cr,
  case when tech_cr=1 then 'true' else 'false' end as tech_cr,
  case when bx_info=1 then 'true' else 'false' end as bx_info,
  case when filmog=1 then 'true' else 'false' end as filmog,
  case when dist_co=1 then 'true' else 'false' end as dist_co,
  case when prod_co=1 then 'true' else 'false' end as prod_co,
  case when cost=1 then 'true' else 'false' end as cost,
  case when illust=1 then 'true' else 'false' end as illust,
  case when biblio=1 then 'true' else 'false' end as biblio,
  d.entered,
  d.entered_by,
  d.modified,
  d.modified_by,
  u.src_id urlsource,
  n2.name_type urlnametype,
  u.docurl,
  n2.lname urlsourcename
FROM docs d
left outer join names n on n.name_id=d.src_id
left outer join docurls u on (d.doc_id=u.doc_id and u.note is null and u.src_id<>486)
left outer join names n2 on n2.name_id=u.src_id
where d.doc_id<>1
order by d.doc_id

SELECT doc_id, d.subj_id, s.subj
FROM docsubjs d
inner join subjects s on s.subj_id = d.subj_id
where d.subj_id<>1
order by doc_id

SELECT nd.doc_id, nd.namesubj_id,
case when n.name_type=2 then 'organization'
    when n.name_type=1 then 'person'
    when n.name_type=3 then 'committee' end as subjectVocab,
    ltrim(n.fname + substring( ' ', sign( datalength( n.fname ) ), 1 ) +
    mname + substring( ' ', sign( datalength( n.mname ) ), 1 ) +
    lname + substring( ' ', sign( datalength( n.gen_suffix ) ), 1 ) +
    n.gen_suffix) as displayName,
    case when n.name_type=1 then 'personauthorities' else 'orgauthorities' end as authtype
FROM namedocs nd
left outer join names n on n.name_id=nd.namesubj_id
where nd.namesubj_id<>1 and nd.doc_id<>1
order by nd.doc_id

SELECT fd.doc_id, fd.film_id, f.title,
    'urn:cspace:cinefiles.cspace.berkeley.edu:workauthorities:name(work):item:name('
        + convert(varchar(10), f.film_id) + ')'''
        + substring(
        substring(
            f.prefix + ' ', 1,
            datalength( f.prefix + ' ' ) -
            sign( patindex( '%[''-]', f.prefix ))),
            sign( patindex( '%[^^I ]%', f.prefix )), 10 ) + f.title
        + '''' as refname
FROM filmdocs fd
left outer join films f on fd.film_id=f.film_id
where fd.film_id <> 1 and fd.doc_id <> 1
order by fd.doc_id

SELECT dl.doc_id, dl.lang_id, l.lang_tla, lang
FROM doclangs dl
left outer join languages l on dl.lang_id=l.lang_id
where dl.lang_id <> 1 and l.lang_id <> 1
order by dl.doc_id

SELECT doc_id,
a.name_id authorShortId,
case when n.name_type=2 then 'organization'
    when n.name_type=1 then 'person' end as authorVocab,
n.fname + substring( ' ', sign( datalength( n.fname ) ), 1 ) +
    mname + substring( ' ', sign( datalength( n.mname ) ), 1 ) +
    lname + substring( ' ', sign( datalength( n.gen_suffix ) ), 1 ) +
    n.gen_suffix as authDisplayName
FROM docauthors a
left outer join names n on n.name_id=a.name_id
where a.name_id<>1
order by doc_id

SELECT
   doc_id,
   convert( varchar(15), doc_id ) as shortIdentifier,
   pages,
   page,
   convert( varchar(5), page ) as pageNumber,
   convert( char(5), datepart(year, entered)) +
      substring( convert( varchar(50), entered, 7), 1,
                 charindex( ',', convert( varchar(50), entered, 7))-1) as displayDate,
   datepart( year, entered ) as earliestYear,
   datepart( month, entered ) as earliestMonth,
   datepart( day, entered ) as earliestDay,
   entered as earliestScalarDate,
   dateadd(day, 1, entered) as latestScalarDate,
   entered,
   modified,
   ISNULL(entered_by, '') as enteredBy,
   ISNULL(modified_by, ISNULL(entered_by, '')) as modifiedBy,
   src_id,
   convert( varchar(15), src_id) as publisherIdentifier

FROM docs d, pages p

WHERE
   d.pages >= p.page
   AND doc_id > 1

Save the output into a file (e.g. "media.list"). The first field is used to sort numerically and can then be removed. The sorting step is useful for uploading image files in smallish blocks with contiguous document IDs.

Nowrun the upload script with the "media.list"

The media record title should reflect the original file name of the attached blob, but when blobs are
attached to an existing media record in a batch, the title does not get updated. After importing blobs, the media records can be updated in the database to set the title correctly.

Note that it is a good idea to wrap the update in a transaction. I included both the BEGIN and COMMIT statements here, but in practice the results should be reviewed before issuing the COMMIT.