SELECT *
FROM (

SELECT DISTINCT DC.ServiceID
FROM tDataCollection DC
WHERE DC.BatchID = 29188

SELECT SID.SupplierAccountID, SID.CustomerCentreID, SID.CustomerSubledgerID, SID.CentreID, SID.ActivityID, SID.SubledgerID, SID.LocationID, SID.InstallationDate, 
(1) AS "Active", GETDATE() AS "DateEntered", GETDATE() AS "DateLastChanged", 'Ron' AS "LastUpdatedBy", (227) AS "ApplicationID"
FROM tServiceID SID
WHERE SID.ServiceID = '0400-039055'

) AS NewSID


INSERT INTO tServiceID
(ServiceID, Service, ServiceTypeID, SupplierAccountID, Active, DateEntered, DateLastChanged, LastUpdatedBy, ApplicationID)
SELECT DISTINCT tDataCollection.ServiceID,
 (SUBSTRING(tDataCollection.ServiceID,1,4)+SUBSTRING(tDataCollection.ServiceID,5,10)) AS "Service", (1878) AS "ServiceTypeID",
 (49) as "SupplierAccountID", (1) AS "Active", GETDATE() AS "DateEntered", GETDATE() AS "DateLastChanged",
 'Ron' AS "LastUpdatedBy", (227) AS "ApplicationID"
FROM tDataCollection
WHERE tDataCollection.BatchID = 29188