Untitled

USE [SupplierPortal.Umbraco.STAGING];
GO

-- Query 1: Check if we can see the old member groups
SELECT COUNT(*) as OldAssignments
FROM [SupplierPortal.LIVE].dbo.cmsMember2MemberGroup;

-- Query 2: Check if email matching is working (with collation fix)
SELECT TOP 10
    m_old.nodeId as OldNodeId,
    m_old.Email as OldEmail,
    m_new.nodeId as NewNodeId,
    m_new.Email as NewEmail
FROM [SupplierPortal.LIVE].dbo.cmsMember m_old
LEFT JOIN [SupplierPortal.Umbraco.STAGING].dbo.cmsMember m_new 
    ON m_new.Email COLLATE SQL_Latin1_General_CP1_CI_AS = m_old.Email COLLATE SQL_Latin1_General_CP1_CI_AS
ORDER BY m_old.nodeId;

-- Query 3: Check what assignments exist in NEW database
SELECT COUNT(*) as NewAssignments
FROM [SupplierPortal.Umbraco.STAGING].dbo.cmsMember2MemberGroup;

-- Query 4: Check the dry run count (with collation fix)
SELECT COUNT(*) as TotalAssignments
FROM [SupplierPortal.LIVE].dbo.cmsMember2MemberGroup m2g_old
INNER JOIN [SupplierPortal.LIVE].dbo.cmsMember m_old ON m2g_old.Member = m_old.nodeId
INNER JOIN [SupplierPortal.Umbraco.STAGING].dbo.cmsMember m_new 
    ON m_new.Email COLLATE SQL_Latin1_General_CP1_CI_AS = m_old.Email COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE NOT EXISTS (
    SELECT 1 FROM [SupplierPortal.Umbraco.STAGING].dbo.cmsMember2MemberGroup m2g_new
    WHERE m2g_new.Member = m_new.nodeId
    AND m2g_new.MemberGroup = m2g_old.MemberGroup
);