I did a constituent import and told the import not to create duplicated but now I have a list of emails that I need to add to already-existing patron records since they didn't get updated, I have the constituent ids. I'm struggling with the best way to get the records updated with the new information and any help would be greatly appreciated!
Thanks,Melissa
If you have the emails and constituent IDs in something like an Excel file, doing a manual import is a pretty simple process if you've got some nominal SQL chops.
You'll start with importing your Excel file to a new local table (Right-click Impresario in SSMS and do Tasks -> Import Data).
Then you'll write a script to insert into T_EADDRESS by selecting from your local table. It's largely up to you how detailed you can get with this -- most columns in T_EADDRESS either have a default constraint or are nullable, and you can do any amount of processing of your local table to find dupes or clean up the data beforehand.
I don't believe the primary key for T_Eaddress is an identity column, so I think you'll have to compute that on your insert using the maximum eaddress_no from T_Eaddress and an identity column from your local table.
Make sure to work everything out in your test system before running on live!
Nick's method would be a great approach, although I want to point out that for whichever method you use, the primary key for T_EADDRESS (eaddress_no) should be pulled through the Next ID function to properly increment the Next ID table. Something like this would work:
declare @next_eaddress_no int
exec @next_eaddress_no = [dbo].AP_GET_NEXTID_FUNCTION @type = 'AD'
-Michael Wilcox, Tessitura Network
Thanks for the replies, this was really helpful!
Michael,
I always look in T_NEXT_ID for a matching parent table, and if I don't find one I assume the primary key for the table is an identity column or a max(id)+1 situation. That row of T_NEXT_ID is indicated for T_ADDRESS, but I can see that there is no overlap in the primary keys of T_EADDRESS and T_ADDRESS tables. Where should I be looking to find this info? The Tessitura Table Structures documentation only mentions T_NEXT_ID on the giftaid_claim_header table...
Hi Nick, no I don't believe this table has documentation. As Brian has noted, the address entry in T_NEXT_ID is used for T_ADDRESS.address_no, T_EADDRESS.eaddress_no, and T_PHONE.phone_no.
Should you face a similar situation in the future, you may want to look through code from related areas of the application (like the Import Utilities that use lots of next IDs), open the application and manually create an entry to see which next ID (if any) gets updated, or ask our Support department.
Yes, I see. I sent a request to the docs team, since I would think this could be easily included in the table structures docs (as it is in one case).