• Replies 2 replies
  • Subscribers 337 subscribers
  • Views 103 views
  • Users 0 members are here
Related

Bulk Add Email Addresses to Existing Constituent Records

Lauren Gruber
$organization

Hi all,

I've got a large list of email addresses that I'd liked to add as Secondary Emails on existing constituent records (that already have Primary Email addresses). I've got the emails matched to constituent IDs all ready to go in a table. Does anyone have a SQL script already written to attach these to their accounts that they'd be willing to share? Trying to avoid reinventing the wheel if possible.

Thanks!

Lauren

Parents
  • $organization

    I don't know what your data looks like, if you already have customer_no in the importing file or not.  Here is something you can use to get you started.

    if exists
    (select * from tempdb.dbo.sysobjects where name like '#email%')
    drop table #email
    select
    null as eaddress_no,
    customer_no,
    '1' as eaddress_type,
    email as address,
    null as start_dt,
    null as end_dt,
    'YYYYYYYYYYYY' as months,
    'Y' as primary_ind,
    'N' as inactive,
    'Y' market_ind,
    '0' as alt_signor,
    null as mail_purposes,
    'brian' as create_loc,
    'dbo' as created_by,
    getdate() as create_dt,
    null as last_updated_by,
    null as last_update_dt,
    'Y' as html_ind
    into #email
    from #customer


    /*--Updating the t_next_id table for address --select * from t_next_id order by parent_table
    IF ISNULL((select max(address_no) from t_address), 0) < ISNULL((select max(eaddress_no) from t_eaddress), 0)
    Update t_next_id
    set next_id = (select max(eaddress_no) from t_eaddress) + 10
    where type = 'AD'
    Else
    Update t_next_id
    set next_id = (select max(address_no) from t_address) + 10
    where type = 'AD'*/

    create table #c1(contact_no int)
    insert into #c1
    select max(phone_no) as contact_no from t_phone
    union
    select max(address_no) as contact_no from t_address
    union
    select max(eaddress_no) as contact_no from t_eaddress
    select max(contact_no) from #c1

    Update t_next_id
    set next_id = (select max(contact_no) from #c1) + 10
    where type = 'AD'

    alter table #email
    add record_no int identity
    go
    update #email
    set eaddress_no = record_no + (select next_id from t_next_id where type = 'AD') + 10

    insert into t_eaddress(
    eaddress_no,
    customer_no,
    eaddress_type,
    address,
    start_dt,
    end_dt,
    months,
    primary_ind,
    inactive,
    market_ind,
    alt_signor,
    mail_purposes,
    create_loc,
    created_by,
    create_dt,
    last_updated_by,
    last_update_dt,
    html_ind)
    select
    eaddress_no,
    customer_no,
    eaddress_type,
    address,
    start_dt,
    end_dt,
    months,
    primary_ind,
    inactive,
    market_ind,
    alt_signor,
    mail_purposes,
    create_loc,
    created_by,
    create_dt,
    last_updated_by,
    last_update_dt,
    html_ind
    from #email

Reply
  • $organization

    I don't know what your data looks like, if you already have customer_no in the importing file or not.  Here is something you can use to get you started.

    if exists
    (select * from tempdb.dbo.sysobjects where name like '#email%')
    drop table #email
    select
    null as eaddress_no,
    customer_no,
    '1' as eaddress_type,
    email as address,
    null as start_dt,
    null as end_dt,
    'YYYYYYYYYYYY' as months,
    'Y' as primary_ind,
    'N' as inactive,
    'Y' market_ind,
    '0' as alt_signor,
    null as mail_purposes,
    'brian' as create_loc,
    'dbo' as created_by,
    getdate() as create_dt,
    null as last_updated_by,
    null as last_update_dt,
    'Y' as html_ind
    into #email
    from #customer


    /*--Updating the t_next_id table for address --select * from t_next_id order by parent_table
    IF ISNULL((select max(address_no) from t_address), 0) < ISNULL((select max(eaddress_no) from t_eaddress), 0)
    Update t_next_id
    set next_id = (select max(eaddress_no) from t_eaddress) + 10
    where type = 'AD'
    Else
    Update t_next_id
    set next_id = (select max(address_no) from t_address) + 10
    where type = 'AD'*/

    create table #c1(contact_no int)
    insert into #c1
    select max(phone_no) as contact_no from t_phone
    union
    select max(address_no) as contact_no from t_address
    union
    select max(eaddress_no) as contact_no from t_eaddress
    select max(contact_no) from #c1

    Update t_next_id
    set next_id = (select max(contact_no) from #c1) + 10
    where type = 'AD'

    alter table #email
    add record_no int identity
    go
    update #email
    set eaddress_no = record_no + (select next_id from t_next_id where type = 'AD') + 10

    insert into t_eaddress(
    eaddress_no,
    customer_no,
    eaddress_type,
    address,
    start_dt,
    end_dt,
    months,
    primary_ind,
    inactive,
    market_ind,
    alt_signor,
    mail_purposes,
    create_loc,
    created_by,
    create_dt,
    last_updated_by,
    last_update_dt,
    html_ind)
    select
    eaddress_no,
    customer_no,
    eaddress_type,
    address,
    start_dt,
    end_dt,
    months,
    primary_ind,
    inactive,
    market_ind,
    alt_signor,
    mail_purposes,
    create_loc,
    created_by,
    create_dt,
    last_updated_by,
    last_update_dt,
    html_ind
    from #email

Children