Very cryptic, but I get your point that I...

This has a comparable execution plan to Paul's submission (uses his setup data):

UPDATE a

SET Age = (SELECT MAX(Age) FROM #TableB b WHERE b.UserID = a.UserID)

FROM #TableA a

Here's yet another way:

DECLARE @t TABLE (status VARCHAR(30))

INSERT INTO @t

SELECT 'On Hold' UNION ALL SELECT 'On Hold' UNION ALL SELECT 'On Hold'

UNION ALL SELECT 'On Hold' UNION ALL SELECT...

Perhaps like this?

DECLARE @t TABLE (col1 DECIMAL(9,0), col2 DECIMAL(9,0))

INSERT INTO @t

SELECT 143445634, 452674354

SELECT col1, col2, CAST(1.*col1/col2 AS DECIMAL(4,3))

FROM @t

Maybe I'm oversimplifying this but here is one possible way:

DECLARE @Products TABLE

(

ProductID integer PRIMARY KEY CLUSTERED,

Quantity...

Perhaps something like this?

DECLARE @primary TABLE (ID INT IDENTITY, Activity VARCHAR(30))

DECLARE @types TABLE (Activity VARCHAR(30), ActGroup VARCHAR(5))

INSERT INTO @primary (Activity)

SELECT 'Running' UNION ALL SELECT 'Jogging'

UNION ALL SELECT 'Walking' UNION...

Jeff Moden (5/18/2012)

---

Ok... since no one else took the testing suggestion...

I didn't document a full spectrum test like Dwain did because I was actually hoping someone besides me would do...

Jeff Moden (5/18/2012)

---

It's like what Dwaine C. did with the "n-tuple" problem... he came up with a way with an rCTE that I wouldn't have considered. I don't believe...

I don't recall that being the error I was getting but since it is pretty explicitly saying that I was trying to be a naughty boy, I suppose I shall...

I must conclude that somehow I must have mucked it up then.

The only difference was that I wasn't doing an INSERT.

I'll have to give it another go sometime because it...

Jeff Moden (5/17/2012)

---

To return precisely what you asked for...

DECLARE @Year DATETIME;

SELECT @Year = '2011';

WITH

cteDate AS

(

SELECT EOM = DATEADD(mm,number,@Year)-1

FROM master.dbo.spt_values v

WHERE Type...

The above debate between Jeff and Paul is precisely the kind of discussion that I was hoping that my article would generate. I am particularly interested in seeing the...

Jeff Moden (5/17/2012)

---

Crud. It's not my day. I have to be honest even if it's not going to look so good for me. :blush: I don't know...

DATE datatype contains no time component and is such better suited for storing things like DOB or a job start date (where time is not important).

GSquared (5/17/2012)

---

If you want to mess around with it a bit, it occurs to me that you could use an Output clause on a Merge statement to end up inserting...