WHILE Loop for data migration/merge with multiple fields for matching

Question

Ok so I have two identical databases in two parts of the world. I need to merge everything into the one I will call North (the other being South). North data trumps South data. I started thinking about a WHILE loop but am not sure that is the best/easiest way to go about this. My plan was to restore a copy of the South database onto the Instance where North lives and then do my work.

The table in question has ID, FirstName, LastName, Email and Login fields. And my rules are:

• Any Login fields that exist in both are logged to a logging table with a status field saying it was not migrated and another for why it did not (because has identical Login).

• If the Login field is NULL in South or does not match with that field in North we must check for a match on the Email field (logging similarly as above if we find a match).

• If neither Login nor Email match the final attempt is FirstName + LastName (has to be both) and logged as above.

• Any Login fields that don't exist in North (destination) but do in South (source) are logged in that same logging table and then inserted into the North table.

So my initial setup was something like this (which pretty sure I screwed up the Loop):

DECLARE @TempID INT
DECLARE @TempCount INT

SET @TempID = 0
SET @TempCount = 1

WHILE @TempCount = 1
BEGIN

DECLARE @SourceID nvarchar(255)
DECLARE @SourceLogin nvarchar(255)
DECLARE @SourceFirstName nvarchar(255)
DECLARE @SourceLastName nvarchar(255)
DECLARE @SourceEmail nvarchar(255)

DECLARE @TargetID nvarchar(255)
DECLARE @TargetLogin nvarchar(255)
DECLARE @TargetFirstName nvarchar(255)
DECLARE @TargetLastName nvarchar(255)
DECLARE @TargetEmail nvarchar(255)

SET @SourceID = (SELECT TOP 1 id FROM #TempExistence);
SET @SourceLogin = (SELECT TOP 1 Login FROM #TempExistence);
SET @SourceFirstName = (SELECT TOP 1 firstname FROM #TempExistence);
SET @SourceLastName = (SELECT TOP 1 lastname FROM #TempExistence);
SET @SourceEmail = (SELECT TOP 1 email FROM #TempExistence);

SET @TargetID = @SourceID (SELECT TOP 1 id FROM [North].dbo.PersonID]);
SET @TargetLogin = @SourceLogin (SELECT TOP 1 Login FROM #TempExistence);
SET @TargetFirstName = @SourceFirstName (SELECT TOP 1 firstname FROM #TempExistence);
SET @TargetLastName = @SourceLastName (SELECT TOP 1 lastname FROM #TempExistence);
SET @TargetEmail = @SourceEmail (SELECT TOP 1 Email FROM #TempExistence);


    INSERT INTO [MigratePersonLog]

    (
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]

    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[MatchStatus] --Matched or not
    ,[MatchArtifact] --What was matched or not matched
    )

VALUES 
    (
    @SourceID
    ,@SourceFirstName
    ,@SourceLastName
    ,@SourceEmail
    ,@SourceLogin

    ,@TargetID
    ,@TargetFirstName
    ,@TargetLastName
    ,@TargetEmail
    ,@TargetLogin
    ,1
    ,'NONE'
    ) 

    --WHERE ID > @TempID
    --ORDER BY ID

    IF @@ROWCOUNT > 0 
        SELECT @TempID = MAX(ID) 
        FROM #TempExistence
    ELSE
        SET @TempCount = 0
END

Not sure this is the right path to take or if I am doing something wrong/less-optimal for the Loop stuff. Thanks in advance for any help!

Answer 1

So I am assuming the tables look something like this (for my easy I am using table variables instead of physical/temp tables):

DECLARE @North TABLE
(
    ID INT NOT NULL,
    FirstName VarChar(20) NULL,
    LastName VarChar(50) NULL,
    Email VarChar(100) NULL,
    [Login] VarChar(20) NOT NULL
)

DECLARE @South TABLE
(
    ID INT NOT NULL,
    FirstName VarChar(20) NULL,
    LastName VarChar(50) NULL,
    Email VarChar(100) NULL,
    [Login] VarChar(20) NOT NULL
)

DECLARE @MigrationPersonLog TABLE
(
    [SourceWfoPersonID] INT NULL
    ,[SourceFirstName] VarChar(20) NULL
    ,[SourceLastName] VarChar(50) NULL
    ,[SourceEmail] VarChar(100) NULL
    ,[SourceLogin] VarChar(20) NULL

    ,[TargetWfoPersonID] INT NULL
    ,[TargetFirstName] VarChar(20) NULL
    ,[TargetLastName] VarChar(50) NULL
    ,[TargetEmail] VarChar(100) NULL
    ,[TargetLogin] VarChar(20) NULL
    ,[Matched] BIT --Matched (= 1) or not (=0)
    ,[MatchArtifact] VarCHar(50) --What was matched or not matched
)

To scsimon's point, SQL really prefers to work in sets instead of iterations. So anything you can do to avoid loops is preferred in almost all situations. I think this should solve basically everything you need. I may not have understood everything you wanted to do with the logging table when it came to inserting new rows into the North table. But I definitely think this should be a framework you can work with. I tried to make the code self-documenting but I can always explain something if that is needed.

--Check for all instances where the Login's Match
INSERT INTO @MigrationPersonLog
(
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]
    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[Matched]
    ,[MatchArtifact]
)
SELECT
S.ID,   --[SourceWfoPersonID]
S.FirstName,    --[SourceFirstName]
S.LastName, --[SourceLastName]
S.Email,    --[SourceEmail]
S.[Login],  --[SourceLogin]
N.ID,   --[TargetWfoPersonID]
N.FirstName,    --[TargetFirstName]
N.LastName, --[TargetLastName]
N.Email,    --[TargetEmail]
N.[Login],  --[TargetLogin]
1,  --[Matched]
'Login' --[MatchArtifact]
FROM @South S
    INNER JOIN @North N
        ON S.[Login] = N.[Login]

--Check for All Instances where the Email's Match
INSERT INTO @MigrationPersonLog
(
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]
    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[Matched]
    ,[MatchArtifact]
)
SELECT
S.ID,   --[SourceWfoPersonID]
S.FirstName,    --[SourceFirstName]
S.LastName, --[SourceLastName]
S.Email,    --[SourceEmail]
S.[Login],  --[SourceLogin]
N.ID,   --[TargetWfoPersonID]
N.FirstName,    --[TargetFirstName]
N.LastName, --[TargetLastName]
N.Email,    --[TargetEmail]
N.[Login],  --[TargetLogin]
1,  --[Matched]
'Email' --[MatchArtifact]
FROM @South S
    INNER JOIN @North N
        ON S.Email = N.Email
    LEFT OUTER JOIN @MigrationPersonLog L --This LEFT OUTER JOIN makes sure we don't double log people who match on LoginID and Email
        ON L.[SourceWfoPersonID] = S.ID
WHERE L.[SourceWfoPersonID] IS NULL

--Check for All Instances where the First Name + Last Name Matches
INSERT INTO @MigrationPersonLog
(
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]
    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[Matched]
    ,[MatchArtifact]
)
SELECT
S.ID,   --[SourceWfoPersonID]
S.FirstName,    --[SourceFirstName]
S.LastName, --[SourceLastName]
S.Email,    --[SourceEmail]
S.[Login],  --[SourceLogin]
N.ID,   --[TargetWfoPersonID]
N.FirstName,    --[TargetFirstName]
N.LastName, --[TargetLastName]
N.Email,    --[TargetEmail]
N.[Login],  --[TargetLogin]
1,  --[Matched]
'FirstName+LastName'    --[MatchArtifact]
FROM @South S
    INNER JOIN @North N
        ON (S.FirstName + S.LastName) = (N.FirstName + N.LastName)
    LEFT OUTER JOIN @MigrationPersonLog L --This LEFT OUTER JOIN makes sure we don't double log people who match on First Name + Last Name AND (LoginID and/or Email)
        ON L.[SourceWfoPersonID] = S.ID
WHERE L.[SourceWfoPersonID] IS NULL


--Log all instances which failed previous 3 checks that they failed the checks
INSERT INTO @MigrationPersonLog
(
    [SourceWfoPersonID]
    ,[SourceFirstName]
    ,[SourceLastName]
    ,[SourceEmail]
    ,[SourceLogin]
    ,[TargetWfoPersonID]
    ,[TargetFirstName]
    ,[TargetLastName]
    ,[TargetEmail]
    ,[TargetLogin]
    ,[Matched]
    ,[MatchArtifact]
)
SELECT
S.ID,   --[SourceWfoPersonID]
S.FirstName,    --[SourceFirstName]
S.LastName, --[SourceLastName]
S.Email,    --[SourceEmail]
S.[Login],  --[SourceLogin]
NULL,   --[TargetWfoPersonID]
NULL,   --[TargetFirstName]
NULL,   --[TargetLastName]
NULL,   --[TargetEmail]
NULL,   --[TargetLogin]
0,  --[Matched]
NULL    --[MatchArtifact]
FROM @South S
    LEFT OUTER JOIN @MigrationPersonLog L --This is used to quickly catch anyone who didn't get logged previously.
        ON L.[SourceWfoPersonID] = S.ID
WHERE L.[SourceWfoPersonID] IS NULL

INSERT INTO @North
(
    ID, --Unless this is and Identity and you can ignore it
    FirstName,
    LastName,
    Email,
    [Login]
)
S.ID,   --ID, 
S.FirstName,    --FirstName,
S.LastName, --LastName,
S.Email,    --Email,
S.[Login]   --[Login]
FROM @South S
    LEFT OUTER JOIN @MigrationPersonLog L --This is used to quickly catch anyone who didn't get logged previously.
        ON L.[SourceWfoPersonID] = S.ID
WHERE L.[SourceWfoPersonID] IS NULL

Hope this give you what you are looking for.