Ditto what Chris said. Varchar(max) is going to hurt your I/O. Go there only if you KNOW your data is going to exceed 8000 characters....

Christian, that is an interesting approach.

Thinking about this, it is also possible to use CTEs to get the totals and then join the CTEs. ...

GK:

Begging your pardon, but your question is unclear to me. Could you give a few examples of your data and what you want your output to look like?

I'm...

You didn't specify where the data is coming from to load your temp table(s). Are you doing a bulk insert, pulling data from a linked server, or what?...

In addition to dates, it's useful for generating any range of numbers, such as invoice numbers, where you might be looking for gaps. If I wanted to test...

Okay..... SLAP !!! 😀

Seriously... CTEs can get the job done in SQL2005. Define two: cteA and cteB, both of which have a row number column...

Assuming invoice number is a unique key in table A, here is another approach, using a summary query:

select A.[invoice no], max([invoice amt]) as invoice_amt,

...

Somewhat embarassing to get caught in typos that increase volumes :w00t:, or quick reads that miss the point being made. But I'll survive.

You never answered the question:...

Dangit... every time I see a Moden-comment I get befuzzled. Do you read every Forum thread? Let me clarify that I don't ever update an...

I never knew it was an undocumented feature. I've been doing it for years. It just seemed intuitively obvious.

As we say down here "Even...

I concur on having in the same proc for mass insert/updates. Separate procs are usually desirable to support single-row transactions through user interfaces.