• Replies 1 reply
  • Subscribers 337 subscribers
  • Views 113 views
  • Users 0 members are here
Related

Bulk Memberships

Christopher Sherwood
$organization

Looking for a sanity check/secondary opinions on this:

Our Membership team is looking to provide non-member donors with complementary trial memberships within our current membership organization.

Few key factors in providing this:

 

  • The memberships aren't going to be tied to contributions
  • The donors for this have never been members

 

Knowing this info we shouldn't have to worry about TX_CONT_MEMB or updating any current or pending records in TX_CUST_MEMBERSHIP.

 

Moving forward I created this script

 

DECLARE @customer INT; DECLARE @cust_memb_no INT; DECLARE @customers_to_update CURSOR; SET @customers_to_update = CURSOR FOR SELECT customer_no FROM T_LIST_CONTENTS WHERE list_no = 1371; --list containing desired non-members DECLARE @tmp_memberships TABLE ( --declare table variable to hold new rows until done cust_memb_no int, parent_no int, customer_no int, campaign_no int, memb_org_no int, memb_level varchar(3), memb_amt money, ben_provider int, init_dt datetime, susp_dt datetime, cancel_dt datetime, renew_dt datetime, expr_dt datetime, lapse_dt datetime, rein_dt datetime, current_status int, NRR_status char(2), memb_trend int, first_issue_id int, last_issue_sent_id int, final_issue_id int, declined_ind char(1), num_copies int, AVC_amt money, orig_expiry_dt datetime, orig_memb_level varchar(10), ship_method int, inception_dt datetime, mir_lock int, create_loc varchar(16), created_by char(8), create_dt datetime, last_updated_by char(8), last_update_dt datetime, last_issue_status int, cur_record char(1), ben_holder_ind char(1), recog_amt money, category_trend int, notes varchar(1024) ); OPEN @customers_to_update; FETCH NEXT FROM @customers_to_update INTO @customer; WHILE @@FETCH_STATUS = 0 BEGIN /*do manipulations here*/ Exec @cust_memb_no = [dbo].ap_get_nextid_function 'CM'; /*insert FROM base, plus manipulations*/ INSERT INTO @tmp_memberships SELECT @cust_memb_no as cust_memb_no, parent_no, @customer as customer_no, campaign_no, memb_org_no, memb_level, memb_amt, ben_provider, init_dt, susp_dt, cancel_dt, renew_dt, expr_dt, lapse_dt, rein_dt, current_status, NRR_status, memb_trend, first_issue_id, last_issue_sent_id, final_issue_id, declined_ind, num_copies, AVC_amt, orig_expiry_dt, orig_memb_level, ship_method, inception_dt, mir_lock, create_loc, created_by, create_dt, last_updated_by, last_update_dt, last_issue_status, cur_record, ben_holder_ind, recog_amt, category_trend, 'Batch Addition; Trial Membership' as notes FROM TX_CUST_MEMBERSHIP WHERE cust_memb_no = 10475 --membership record to copy FETCH NEXT FROM @customers_to_update INTO @customer; END CLOSE @customers_to_update; DEALLOCATE @customers_to_update; INSERT INTO TX_CUST_MEMBERSHIP SELECT cust_memb_no, parent_no, customer_no, campaign_no, memb_org_no, memb_level, memb_amt, ben_provider, init_dt, susp_dt, cancel_dt, renew_dt, expr_dt, lapse_dt, rein_dt, current_status, NRR_status, memb_trend, first_issue_id, last_issue_sent_id, final_issue_id, declined_ind, num_copies, AVC_amt, orig_expiry_dt, orig_memb_level, ship_method, inception_dt, mir_lock, create_loc, created_by, create_dt, last_updated_by, last_update_dt, last_issue_status, cur_record, ben_holder_ind, recog_amt, category_trend, notes FROM @tmp_memberships;

Does this look about right to everyone?



[edited by: Christopher Sherwood at 5:15 PM (GMT -6) on 6 May 2014] trying inline instead....