I've seen this sort of "inconsistent" result on UPDATEs where you're applying multiple rows to a single row and I don't believe it is controlled by a setting.

You need to...

GSquared (9/19/2012)

---

Phil Parkin (9/19/2012)

---

One thing which I have noticed with MERGE is that it does not natively provide separate Updated/Inserted/Deleted counts.

You need to code an OUTPUT clause and use $Action...

Not sure but considering your latest requirements, I think a slight modification to Lowell's original suggestion might perform better:

With MyCTE (Product,Parts,Status)

AS

(

SELECT 'Laptop1','mouse','OK' UNION ALL

SELECT 'Laptop1','screen','OK' UNION ALL

SELECT...

Incidentally, a slight change to the table DDL and adding an index had a nice impact on my second solution's elapsed time:

CREATE TABLE #Customer

( CustKey INT ...

egerencher (9/19/2012)

---

"Firstly, I'd like to question why trans# 3 and 6 appear in your expected results. The total charges for period 02-03 Jan is $510 for 123456789 and for 02-03...

egerencher (9/19/2012)

---

You guys are awesome. I can't wait to get back to work to try this out. I will report back the results tomorrow. I'm a SQL...

FYI. These were my timing stats on 960,000 transactions:

SQL Server Execution Times:

CPU time = 17815 ms, elapsed time = 99414 ms.

David - Really nice explanation of your approach. I have to say mine falls short in that regard.

I think it's important to look at the whole picture though. ...

Lowell (9/19/2012)

---

there's two ways to do it;

one witht he proprietary SQL syntax, and another with a correlated query:

--SQL Server UPDATE FROM syntax

UPDATE [Projects]

SET Projects.Closed = Projects3.Closed

FROM [Projects3]

WHERE Projects3.Name =...

Firstly, I'd like to question why trans# 3 and 6 appear in your expected results. The total charges for period 02-03 Jan is...

robert.gerald.taylor (9/19/2012)

---

Couldn't you just unpivot your data? Something like:

SELECT DISTINCT SpecificCols

FROM

(SELECT col1, col2, col3, col4

FROM @t) t

UNPIVOT

(SpecificCols FOR AllCols...

Timothy Graffham (9/19/2012)

---

I was very excited to go to my post this morning and see 8 responses. I thought I was going to get some real help. Instead,...

You might want to take a look at this article by Jeff Moden to get you started: http://www.sqlservercentral.com/articles/T-SQL/71550/, although that endpoint requirement is a bit odd and may take some...

Yasemin Örnek (9/19/2012)

---

Hi , I want to ask a question ,

Is it possible executing dynamic sql and store values in a temp table with WITH clause like below code?

DECLARE...

As a side bar, here is one of the reasons I shouldn't be...