• Replies 5 replies
  • Subscribers 416 subscribers
  • Views 102 views
  • Users 0 members are here
Related

SQL Help

Chris Howarth
$organization

I was going to go through a lot of trial and error but one of you must have this sitting somewhere.  I'd like to insert attributes on mass, I'm assuming to the tx_cust_keyword, based on a constituency. Any SQL you'd like to share?

 

Chris

Parents
  • $organization

    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

Reply
  • $organization

    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

Children
No Data