-- create all missing root connections
with cteRepository as (select ID as RepositoryID, newid() as ID from dbo.Repository where RepositoryType = 0 union all select '00000000-0000-0000-0000-000000000000', newid())
,cteGoodRoots as (select isnull(RepositoryID, '00000000-0000-0000-0000-000000000000') as RepositoryID, max(Version) as Version from dbo.Connections where ConnectionType = 92 group by RepositoryID)
insert into dbo.Connections (ID, DATA, ConnectionType, ConnectionSubType, GroupName, Name, DataSourceID, MetaData, CreationDate, Description, ModifiedDate, ModifiedUsername, ModifiedLoggedUserName, RepositoryID) 
select r.ID, N'<?xml version="1.0"?>
<Connection>
  <ConnectionType>Root</ConnectionType>
  <CreatedBy>upgrade-script</CreatedBy>
  <CreationDateTime>' + convert(nvarchar(19), getutcdate(), 126) + '</CreationDateTime>
  <ID>' + convert(nvarchar(36), r.ID) + '</ID>
  <Name>[Root]</Name>
</Connection>', 92, N'', N'', N'[Root]', '00000000-0000-0000-0000-000000000000', N'<?xml version="1.0"?>
<RDMOConnectionMetaData>
  <ConnectionType>Root</ConnectionType>
  <Name>[Root]</Name>
</RDMOConnectionMetaData>',
       GETUTCDATE(), null, GETUTCDATE(), N'upgrade-script', N'upgrade-script', r.RepositoryID
from cteRepository r left outer join cteGoodRoots gr on r.RepositoryID = gr.RepositoryID
where gr.RepositoryID is null;