That seems to do it. I'd experimented with case and isnull but never got as far as using them using together. Thanks for this!

One more question. I'm currently on 2005 and hope to move to 2008 R2 shortly. I haven't looked into yet but I was thinking about changing my 'upserts' to using...

Wow, thanks Paul - great article! I'd got as far as seeing the problems in writing out the combinations, coalesce and isnull, but would never have thought of using intersect...

Sorry, haven't got access to my sql code at the moment but the example I've given matches it. The only nullable column is A and it's a one-to-one relationship between...

count(@A) will work in the cut-down example I posted using only variables, a select without any table referenced. count(ColA) won't of course work without a sub-query.

I came across the...

Thanks for that - never thought of doing count(@A) to test for null. Learnt something new!

Yes, I got caught out with nulls (not for the first time!) and started thinking about it. Maybe it's better to avoid isnull or coalesce and use:

@A1 = @A2 or...

Of course not, I just had no idea what it would do. There's nothing special about isnull(@A, ''), you're replacing a null with a value for comparison because you didn't...

declare @a int

select @a = null

select isnull(@A, '')

This returns zero.

isnull(@A, '') will work when @a is char (and empty string is not in the db) but not when @a is numeric.

You're still left with isnull(@A,...

Thanks for that. Agree about avoiding null columns if at all possible - something I know but then didn't do and got caught out!

A default value is relatively easy to...

Thanks.

There doesn't seem to be the restriction of the where clause being for a single sub-table insert per pass (if I understand this right) as a single:

insert PartionedView...

select ... from...

Four base tables for 2008 or earlier, 2009, 2010, 2011 or later - first one shown:

CREATE TABLE [dbo].[Migrate2008HHTable](

[MeterPointRef] [nvarchar](50) NOT NULL,

[MeterRef] [nvarchar](50) NOT NULL,

[Rdg_Timestamp] [smalldatetime] NOT NULL,

[Value] [decimal](18, 4) NOT...

Ok. What about updating the partitioned view with another table, can't see how this can be done without referencing the view in the update to find the matching rows?

Yes, that's very useful for the initial populating of the view base tables. But thinking there is a more fundamental problem that would affect existing stored procedures that are used...