SQL Help

Hi

I use the following in a scheduled job that checks for new constituency members and adds the attribute.

First, everyone with the attribute is deleted - you might not need to do this but I like to to make sure the database is clean and anyone who has has the constituency removed also has the attribute removed:

DELETE FROM impresario.dbo.tx_cust_keyword where keyword_no = @keyword_no and key_value=@key_value

Then run the following, replacing the @ text with your own entries (apart from @customer_no1 in the cursor):

CREATE TABLE #attribute
(customer_no int)

insert into #attribute
--get all customer numbers with selected constituency
select distinct customer_no
from tx_const_cust
where constituency=@constituency


--    put in cursor to process

    declare mycur1 insensitive cursor for
    Select customer_no
    FROM #attribute
    ORDER BY customer_no

select * from #attribute

    declare @customer_no1 int

    open mycur1

    fetch mycur1 into
    @customer_no1

    while @@fetch_status >= 0
       
BEGIN


--insert attribute


INSERT INTO impresario.dbo.tx_cust_keyword ( customer_no, keyword_no, key_value, n1n2_ind ) VALUES ( @customer_no1,
@keyword_no, @key_value, @n1n2_ind )

       
        fetch next from mycur1 into
        @customer_no1

        END

    Drop table #attribute

    close mycur1
    deallocate mycur1

Works for me anyway!

Siobhan x

Hi,  There is a shared web application in footprints that I created which allows you on assign and unassign attributues on mass.  It runs as a report.  It allows you to pick a list, choose the customer type, the attribute, the attribute value, whether or not to assign to name1,name2, or both.  This utility also allows for you to assign multiple values for an attribute (depnding if the attribute allows it) to a customer.    It is called Atribute Blaster and it material number 288 in footprints.

--This will apply the desired attribute and value to --accounts in a specified list if the attribute/value --combination is not already present.

declare @list_no int,
             @attribte_no int,
             @attribute_value  varchar(5)
   
 
    set @list_no = 22593
    set @attribte_no =  405
    set @attribute_value = '06/07'
--=======================================--
--select all from the LIST (FOR CHECKING)--
--=======================================--
                                                /*
SELECT *
    FROM     T_LIST_CONTENTS
        WHERE LIST_NO = @list_no
                                                */
--==================================--
--Create attribute for those in list--
--==================================--
    INSERT Tx_Cust_Keyword
            (keyword_no,
            customer_no,
            key_value)

        SELECT    @attribte_no,
            customer_no,
            @attribute_value

        FROM T_LIST_CONTENTS
            WHERE LIST_NO = @list_no
                AND CUSTOMER_NO NOT IN
                        (SELECT CUSTOMER_NO
                           FROM Tx_Cust_Keyword
                              WHERE keyword_no = @attribte_no
                                AND key_value = @attribute_value)

Thanks every body!  Good old tessnet-technical.