SELECT b.WEA,
       b.[Date_Time],
       a.[SSI1] [SSI1Reference],
       a.[SSI2] [SSI2Reference],
       a.[SSI3] [SSI3Reference],
       b.[SSI1],
     ...

SELECT b.WEA,
   b.[Date_Time],
   a.[SSI1] [SSI1Reference],
   a.[SSI2] [SSI2Reference],
   a.[SSI3] [SSI3Reference],
   b.[SSI1],
   b.[SSI2],
   b.[SSI3]
FROM [dbo].[RT_Group_Coverage] a
INNER JOIN [dbo].[RT_Group_Status] b
   ON b.WEA...

In this instance you might find the following code faster:
;WITH CTE AS
(
SELECT c.Dealer, c.ContCode, CG.Description AS [Attempt], c.RegNo, c.Salute, c.Updated, c.ContDate, c.CompleteCode,

You will need indexes on the columns you are joining on to get good performance. 
When deleting rows from tables that have FK's on different tables pointing to it the...

--QUERY 1
;with cte as
(
  SELECT c.Dealer, c.ContCode, CG.Description AS [Attempt], c.RegNo, c.Salute, c.Updated, c.ContDate, c.CompleteCode
  from #Contacts c
  JOIN #Dealers2 AS d WITH(NOLOCK)...

Can you list the columns you want in "QUERY 1"? There is currently more than one "dealer" column output.

INSERT INTO LOCAL_Item VALUES ('64082','Null', 'Ear Defenders')
should be
INSERT INTO LOCAL_Item VALUES ('64082',Null, 'Ear Defenders')
i.e. you can't have quotes around the Null

Thanks Harsha, a great script (5 stars awarded).
This saved me a lot of effort as I had to detach and then reattach about 50 databases on two instances...