Email Append

 

This is my favorite way of doing it, so that you don’t have to deal with a cursor or while loop.

BWG

 

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

 

 

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

 

 

--Updating the t_next_id table for address

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'

 

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Levi Sauerbrei
Sent: Thursday, October 03, 2013 10:41 AM
To: Brian W. Grundstrom
Subject: Re: [Tessitura Technical Forum] Email Append

 

Shereen,

I think the idea of putting the emails in their own type during the append has a lot of merit.  It is fairly easy to change the type later.  Much easier than trying to untangle things later.  The other thing I often do with large data adds from an outside source is to script the "created_by" field to something distinct (like "eapp1013"). This will allow you to round up all of those emails later if you need to identify them.

I don't know of any utilities that make this easier.  But I do have a couple of scripts that I've used in the past.  You would need to look through them and customize them, But the biggest advantage is that I've built a WHILE loop that helps you grab the right values from T_NEXT_ID as the inserts are being done.

Hello,

I've been asked to complete an email append in the very near future for our organization, TPS.  We're the master license holder of a new consortium and are working through several projects in preparation for the first consortium merge - there are potentially 52K duplicate accounts currently.  Additionally, our email addresses have not yet been moved off the General Tab.  Tessitura will be contracted to assist with the pre-merge tasks and the consortium merge but not before this email append is due.  As a result, there's the possibility that some of the append emails may be for patrons who have accounts not only with our organization but one or both of the other consortium members.  What are my options for the email append (SQL scripts, utilities, etc.) and what might be the best strategy given the challenges of the consortium and many duplicate accounts?  Would it make sense to create a new eaddress type for these append emails and use a SQL script to add them to the T_EADDRESS table?  Is there a utility that would work better in this situation?  I appreciate any suggestions you might have.

Thank you,

Shereen 

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

 

This is my favorite way of doing it, so that you don’t have to deal with a cursor or while loop.

BWG

 

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

 

 

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

 

 

--Updating the t_next_id table for address

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'

 

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Levi Sauerbrei
Sent: Thursday, October 03, 2013 10:41 AM
To: Brian W. Grundstrom
Subject: Re: [Tessitura Technical Forum] Email Append

 

Shereen,

I think the idea of putting the emails in their own type during the append has a lot of merit.  It is fairly easy to change the type later.  Much easier than trying to untangle things later.  The other thing I often do with large data adds from an outside source is to script the "created_by" field to something distinct (like "eapp1013"). This will allow you to round up all of those emails later if you need to identify them.

I don't know of any utilities that make this easier.  But I do have a couple of scripts that I've used in the past.  You would need to look through them and customize them, But the biggest advantage is that I've built a WHILE loop that helps you grab the right values from T_NEXT_ID as the inserts are being done.

Hello,

I've been asked to complete an email append in the very near future for our organization, TPS.  We're the master license holder of a new consortium and are working through several projects in preparation for the first consortium merge - there are potentially 52K duplicate accounts currently.  Additionally, our email addresses have not yet been moved off the General Tab.  Tessitura will be contracted to assist with the pre-merge tasks and the consortium merge but not before this email append is due.  As a result, there's the possibility that some of the append emails may be for patrons who have accounts not only with our organization but one or both of the other consortium members.  What are my options for the email append (SQL scripts, utilities, etc.) and what might be the best strategy given the challenges of the consortium and many duplicate accounts?  Would it make sense to create a new eaddress type for these append emails and use a SQL script to add them to the T_EADDRESS table?  Is there a utility that would work better in this situation?  I appreciate any suggestions you might have.

Thank you,

Shereen 

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!