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!