Sample interest weighting procedure

Hi Gawain. Thank you for the input.  Unfortunately, I am not very familiar with formatting code on my own.  Would you be able/willing to lend a hand with updating that statement to update using the Merge statement like you mentioned? It would be greatly appreciated!

Haha!  I was hoping you wouldn't ask that.  I'm just starting to figure out MERGE myself (and very stupidly annihilated almost all of our Interests a few months ago because I included a DELETE clause without thinking it through -- we were able to recover them from a backup, thanks Support!)

Is it embarrassing to admit that I just never liked dealing with MERGE, so I just always UPDATE existing, DELETE those from my temp table and then INSERT the new ones?

I'm warming to it, but it is a sharp tool.

Also, is it one of the ones that bedevils intellisense?  Can't remember.

I cannot recall either.  I guess a part of me just always feels a little safer separating out the actions on their own.  And nothing I am doing with that is ever so database intensive (and usually is done at 3:00 AM) that it makes even the slightest difference in terms of operational capacity.

I do feel like once I become comfortable with it, it will also make what I'm doing easier for me to read in the future by pulling all the logic into one place.

And becoming more comfortable with and expanding your knowledge on a standard SQL process is rarely ever a bad thing.

Unless other people find out your skills and line up in front of your desk to do all their jobs.  That's why it's better to be SQL Batman and solve database crimes out from the shadows.

oooh a new toy.  I'll have a look at Merge and your error Dan Crowell and get back to you.  My memory is reminding me that I had the same error a couple of times

I too must confess that I just use UPDATE existing, DELETE and INSERT new. I haven't applied it to interest weighting but have a job setup for updating/adding ranking

Hi Heath, sorry for the delay in responding.  It's been hectic over here.  I appreciate your helping to take a look when you get around to it.  It's a big help and unfortunately, I am only a basic SQL user and don't have knowledge on writing code, just simply applying it.  (le sigh)

Hi Dan, not sure if solved this one but if you're basing your script on the in Sadie's post above you should just be able to replace the following section:

--insert new rows for any which are not in the table
insert into tx_cust_tkw
select t.customer_no, t.tkw, 'N', t.weight, substring(@@servername,1,16),'PROCESS', getdate(), null, null
from #tkw t
where tkw_update = 'N'

with this:

--insert new rows for any which are not in the table
insert into tx_cust_tkw (customer_no, tkw, selected, weight)
select t.customer_no, t.tkw, 'N', t.weight
from #tkw t
where tkw_update = 'N'

Hi Thomas, so sorry for the long delay in responding.  Thank you so much for sending over that new code...it worked!!  Eureka!!

Thomas, I was able to get this procedure to work with your help, but I am struggling again with the object #tkw already being in the database. You mentioned doing an UPDATE existing, DELETE and INSERT new query.  Would you be able to help me format that properly for this #tkw "temp table"? It would be a HUGE help.

Thomas, I was able to get this procedure to work with your help, but I am struggling again with the object #tkw already being in the database. You mentioned doing an UPDATE existing, DELETE and INSERT new query.  Would you be able to help me format that properly for this #tkw "temp table"? It would be a HUGE help.

As a general rule, when I am using temporary tables ("#table_name") I make a point to delete any table by that name before creating it, e.g.

IF OBJECT_ID('tempdb..#temp_promo_order', 'U') IS NOT NULL
    DROP TABLE #temp_promo_order
GO