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

You can insert into T_ASSOCIATION but there are serveral considerations. You need to make sure you keep association numbers in line with the T_NEXT_ID table and make sure that table is updated after an ID is added. You have to make sure you properly set up reciprocal assocation numbers. You have to make sure the association types match up properly. 

I'm actually doing this currently and am running into some issues. It looks like it can be done but it's not as simple as inserting a line directly into the table. More logic is required. 

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'

Also - putting the data into a temp table isn't strictly necessary. You could do it all at the insert but I like to use the temp table because I can look over the data before I actually do the insert step.