Well John I agree if such addresses exists in UK then obviously my code will fail. May be there is a lot I need to learn about UK. A newbie...

John - Didn't understood that how code will fail there? May you please elaborate a bit.

Below works! If I understood correctly 🙂

create table #test (

master_idint,

Titlevarchar(20),

Forenamevarchar(50),

Surnamevarchar(50),

Address1varchar(200),

Address2varchar(200),

Address3varchar(200),

Address4varchar(200),

Townvarchar(100),

Countyvarchar(100),

Postcodevarchar(20),

Hierarchyint

)

insert into #test

select 75984,'Mr','O','SQLady','6 Vivien Avenue Midsomer Norto','Midsomer Norton',NULL, NULL, 'RADSTOCK',NULL, 'BA6 2VG',2 union all

select 76144,'Mrs','N','Replacemento','56 Vivien Avenue Midsomer Nort','Midsomer Norton',NULL,...