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...

Yes, that's what I'm after. Tried batching:

declare @BatchSize int

select @BatchSize = 200000

while 1 > 0

begin

begin tran

insert into Destination

...