• Replies 4 replies
  • Subscribers 337 subscribers
  • Views 52 views
  • Users 0 members are here
Related

Any SQL or other way to mass update a constituent with relationships?

Luis Castro
$organization

Hello, looking for a way other orgs may be going about mass updating/populating relationships for a constituent record. We have several of these situations for a board member sends us a list of their known associates and it would be great to be able to plug something in that does most of the work for us. Another scenario would be when/if we receive a list of employees for a particular company. How is everyone currently handling those situations, is the only route to go going manually for each one? Or is there a sql procedure/code someone likes to use? 

Thanks for any and all thoughts!

Parents
  • $organization

    I'm interested in hearing from the hive mind, and you might want to post this in the DBA group or raise in in tomorrow's meetup.

    You can update T_AFFILIATION like 

    BEGIN TRANSACTION

INSERT into T_AFFILIATION (affiliation_no ,individual_customer_no, group_customer_no, affiliation_type_id, is_allowed_to_transact, is_included_in_search_results, inactive)
VALUES 
(12346, XXXXXX, YYYYYY, 10007, 'N', 'N', 'N'), 
(12347, XXXXXX, YYYYYY, 10007, 'N', 'N', 'N')

UPDATE T_NEXT_ID
SET next_id = (select MAX(affiliation_no)+1 from T_AFFILIATION)
WHERE type = 'RP'

ROLLBACK TRANSACTION -- COMMIT TRANSACTION when satisfied

    Where group_customer_no is the org. 

    T_ASSOCIATION is similar but different.

    T_NEXT_ID is assuming v15.X 

    NB/

    1. Add in your favourite change catching SQL
    2. Do it in TEST SSMS first
    3. try to add an affiliation & association in the client after (if it kicks up it could be the T_NEXT_ID)
  • $organization in reply to Heath Wilder

    I'd recommend using the stored procedure dbo.AP_GET_NEXTID_function: although most are not, the point of the "next_id" columns is that they might be shared amongst multiple tables, so a max(id) of one table might not give you (or properly set) the actual next id.  As a bonus, if you use it your code will be v16 safe, as it becomes a wrapper for the sequences that replace T_NEXT_ID,

Reply
  • $organization in reply to Heath Wilder

    I'd recommend using the stored procedure dbo.AP_GET_NEXTID_function: although most are not, the point of the "next_id" columns is that they might be shared amongst multiple tables, so a max(id) of one table might not give you (or properly set) the actual next id.  As a bonus, if you use it your code will be v16 safe, as it becomes a wrapper for the sequences that replace T_NEXT_ID,

Children