SQL code to switch fields on certain constituent addresses?

This is something you could do using SQL, Of course, do the development work in TEST first before running it in PROD. Maybe someone here has something like this written already?

You'll want to be careful to ensure that the entries are consistent though - you wouldn't want to switch ones that are correct. This isn't something that someone unfamiliar with SQL should do though, as it is going to take multiple steps. 

I think I'd start with a list. Scan the list to see how many of entries are correct and, like you said, note them. The correct ones could be used in an exclusion table so that you don't update something that is correct. Then you could create a table with the addresses and business names for each constituent ID and simply overwrite what exists with what is in the temp table.

If you're not super handy with SQL, Extended Services could probably knock this out pretty quickly.

Hope this helps.. 

Business Name in Street 2 is a pretty common convention.  I wonder if it would be better to see if your process for outputting addresses could be adjusted?  One concern I would have is that there are other processes which might expect a proper street address in street1, for instance duplicate identification.

We generally put business names in Salutation 2 for that very reason.

Hi Elizabeth, 

Did you get this figured out yet? If not, I think it would be pretty easy to fix with SQL. This is a quick script I just whipped up that should get the job done. You'll want to make sure to test it first in TEST. You'll just need to create a static list first with all the accounts that need fixing:

--------

drop table if exists #add_fix

create table #add_fix (customer_no int, address_no int, street1_new varchar(255), street2_new varchar(255))

insert into #add_fix (customer_no, address_no, street1_new, street2_new)

select
lc.customer_no,
ad.address_no,
street2,
street1
from T_LIST_CONTENTS lc
join T_ADDRESS ad on lc.customer_no = ad.customer_no and primary_ind = 'Y'
where lc.list_no = #### --enter the list number here for the list you created with all accounts you need to fix

select * from #add_fix --for testing purposes

begin transaction

update T_ADDRESS
set street1 = af.street1_new, street2 = af.street2_new
from #add_fix af
join T_ADDRESS ad on af.address_no = ad.address_no
where ad.address_no = af.address_no

--this is for testing purposes to see the results of the update before committing the change to the database
select * from T_ADDRESS ad1
join T_LIST_CONTENTS lc1 on ad1.customer_no = lc1.customer_no where lc1.list_no = #### and primary_ind = 'Y'  --enter the list number from above

Rollback transaction --when you have tested this script and you are happy with the results, change the word "rollback" to "commit"

----------

Hopes this helps, feel free to reach out if you have any questions, or something isn't working the way you expect. Good luck!

David

Ah, I was not aware of this. The problem I ran into with the output is that it included addresses for households, individuals, and organizations, for printing on envelopes for our holiday cards. So while the individual/household envelopes looked fine with Salutation|Street 1|Street 2, the envelopes for organizations looked wonky. 

I'll give this a think, but maybe it just has to stand that if we're sending one mailing list to the printer we have to do some manual cleanup in Excel first. 

Thanks so much!  I'm looking into the points that Gawain and Anne made, so I may end up not doing the switch. But either way, your script will be useful in teaching me some new SQL skills! 

Could you split your list into two: one for organizations?  Then you could assemble a slightly different output set to reverse the Street1/Street2 columns for that list.