Create Association En Masse

Hi Ragan,

Is the school disctrict also a constituent (It's an attribute for us)?  If so (and you have SSMS access) I think that it'd be pretty simple to insert into the T_ASSOCIATION table.  I'm about to do a similar thing for the T_AFFILIATION table with media orgs and reporters.

Cheers,

Heath

Hi Heath,

I know this is an old thread, but were you able to successfully add affiliations en masse and if so, would you be willing to share the script you ran?  We are currently going through a back-log of contacts that were never added to our database and looking to make it as efficient as possible!

Thanks!

Anne

I can't speak for Heath but I've got a process to automatically create affiliations with accounts/students and high schools. 

The first thing I do is create temp table that houses all of the data that will be inserted into the T_AFFILIATION table. This allows me to properly create the affiliation ID as well. The "meat" of the data - in this case the student and their high school constituent IDs (#HighSchoolsWithTessID)- would be supplied by you and isn't something I can give you. 

The affiliation ID is constructed using the T_NEXT_ID table. 

(Copy and paste the following into SQL server or visual studio for better readability)

Declare
@IDCount int =
(Select next_id From T_NEXT_ID n where n.type = 'Rp')

Select distinct
@IDCount + row_number() over (order by (select NULL)) ID
--, h.cust_type
--, h.lname
--, I.local_use0
, D.StudentTessID as individual_customer_no
, D.FromHSMasterTessID as group_customer_no
, 10028 as Affiliation_type_id
, 'N' is_allowed_to_transact
, 'N' is_included_in_search_results
, null title
, null salary
, null note
, null start_dt
, null end_dt
, 'N' as inactive
, 'N' as Primary_ind
, 0 name_ind
, null affilated_name
, null address_no
, null eaddress_no
, null salutation_no
into #tmpAffilation
From #HighSchoolsWithTessID D

--Actually insert the data from the above query
Insert into T_AFFILIATION(affiliation_no, individual_customer_no, group_customer_no, affiliation_type_id, is_allowed_to_transact
, is_included_in_search_results, title, salary, note, start_dt, end_dt, inactive, primary_ind, name_ind, affiliated_name, address_no, eaddress_no, salutation_no)
Select distinct *
From #tmpAffilation
Where group_customer_no <> ''

--Update the ID table after the insert
Update T_NEXT_ID
Set next_id = (select max(affiliation_no) + 1 From T_AFFILIATION)
Where type = 'RP'

I can't speak for Heath but I've got a process to automatically create affiliations with accounts/students and high schools. 

The first thing I do is create temp table that houses all of the data that will be inserted into the T_AFFILIATION table. This allows me to properly create the affiliation ID as well. The "meat" of the data - in this case the student and their high school constituent IDs (#HighSchoolsWithTessID)- would be supplied by you and isn't something I can give you. 

The affiliation ID is constructed using the T_NEXT_ID table. 

(Copy and paste the following into SQL server or visual studio for better readability)

Declare
@IDCount int =
(Select next_id From T_NEXT_ID n where n.type = 'Rp')

Select distinct
@IDCount + row_number() over (order by (select NULL)) ID
--, h.cust_type
--, h.lname
--, I.local_use0
, D.StudentTessID as individual_customer_no
, D.FromHSMasterTessID as group_customer_no
, 10028 as Affiliation_type_id
, 'N' is_allowed_to_transact
, 'N' is_included_in_search_results
, null title
, null salary
, null note
, null start_dt
, null end_dt
, 'N' as inactive
, 'N' as Primary_ind
, 0 name_ind
, null affilated_name
, null address_no
, null eaddress_no
, null salutation_no
into #tmpAffilation
From #HighSchoolsWithTessID D

--Actually insert the data from the above query
Insert into T_AFFILIATION(affiliation_no, individual_customer_no, group_customer_no, affiliation_type_id, is_allowed_to_transact
, is_included_in_search_results, title, salary, note, start_dt, end_dt, inactive, primary_ind, name_ind, affiliated_name, address_no, eaddress_no, salutation_no)
Select distinct *
From #tmpAffilation
Where group_customer_no <> ''

--Update the ID table after the insert
Update T_NEXT_ID
Set next_id = (select max(affiliation_no) + 1 From T_AFFILIATION)
Where type = 'RP'