I use SQL Server 2016. I need to move users from a legacy system to a new one. But before bringing all users from the legacy system, I need to check if they do not already exist in our new system. Such a determination is done based on comparison of users' email addresses in each of the system.
So if a user does not exist in the new system, I need to insert such a record into the new system from the legacy one. But if user does exist, I need to update some columns in the new system with data from the legacy one.
To accomplish it I'm using a cursor with SCOPE_IDENTITY().
Also I need to log newly inserted and existing updated records in each cursor iteration. The problem is that my code does not differentiate new and existing records hence it inserts all user id in the Log table twice. One time in case the userID (@ContactID) IS NULL (not existing in the new system) and another when userID (@ContactID) IS NOT NULL - existing in the new system.
In my sample below I have 1 records that does not exists in the new system and 3 do match already. So in the IF (@CoantactID IS NULL) I meant to insert only that newly inserted into the MyDB.dbo.Contact table record and in the IF (@CoantactID IS NOT NULL) insert into the Log table only the tree records matched (existing) in the new system.
But the problem is that it inserts all four ContactIDs into the log table even in the this IF (@ContactID IS NULL) amd then again for in IF (@ContactID IS NOT NULL)
;USE [MyDB];
GO
-- exec dbo.sp_UserMigration_Users_Copy
DROP PROCEDURE IF EXISTS dbo.sp_UserMigration_Users_Copy
GO
;USE [MyDB];
GO
CREATE PROCEDURE dbo.sp_UserMigration_Users_Copy
@UserID UNIQUEIDENTIFIER = NULL
--,@ContactID INT
AS
DECLARE @Email nvarchar(100),
@ContactID INT;
-- @UserID UNIQUEIDENTIFIER,
DECLARE @SysID INT
SET @SysId = 17511; -- system contactID
---- creating log table
--CREATE TABLE #T
--(
-- UserID UNIQUEIDENTIFIER NOT NULL,
-- Email NVARCHAR(50) NOT NULL
--);
SELECT * INTO #T
FROM MyDB.dbo.User
WHERE UserID IN (
'0604C514',
'C1FDAF34',
'23BABE2D',
'EBA21D10'
);
IF NOT EXISTS (select * from MyDB.sys.tables where name = N'UserIDContactIDMigrationLog')
CREATE TABLE MyDB.dbo.UserIDContactIDMigrationLog
(
UserID UNIQUEIDENTIFIER /* CONSTRAINT [PK_UserIDContactIDMigrationLog_UserID] PRIMARY KEY */ NOT NULL, -- somehow inserts duplicate UserIDs
ContactId INT /*UNIQUE CONSTRAINT [UNIQUE_ContactId] */ NOT NULL,
CreatedDt DATETIME2 NULL,
UpdatedDt DATETIME2 NULL,
UpdatedFlag BIT /*CONSTRAINT [DF_MigratedFlag] DEFAULT(0) */ NULL
);
-- -----------------------------------------------------
-- Cursor: For all contacts to be migrated
-- -----------------------------------------------------
IF (SELECT CURSOR_STATUS('global','user_cursor')) >= -1
BEGIN
IF (SELECT CURSOR_STATUS('global','user_cursor')) > -1
BEGIN
CLOSE user_cursor
END
DEALLOCATE user_cursor
END
DECLARE user_cursor CURSOR
FOR
SELECT a.UserID, LTRIM(RTRIM(a.Email)) AS Email
FROM #T a WITH (NOLOCK)
--WHERE a.UserID = @UserID
-- begin cursor loop
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @UserID, @Email
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ContactID = (
SELECT c.ContactId
FROM MyDB.dbo.Contact c
LEFT OUTER JOIN MyDB.dbo.Login l ON c.ContactId = l.ContactId
WHERE (LTRIM(RTRIM(c.ContactEmailTx)) = @Email
OR LTRIM(RTRIM(l.ContactLoginNameTx)) = @Email
--OR l.ContactLoginNameTx LIKE @Email+'%' -- this concept does not work in case of leading space at the ContactLoginNameTx so need to use the code below
OR SUBSTRING(LTRIM(RTRIM(l.ContactLoginNameTx)), -1, CHARINDEX('.INACTIVE.', l.ContactLoginNameTx)) = @Email)
AND (c.ContactEmailTx != '' OR c.ContactEmailTx IS NOT NULL OR l.ContactLoginNameTx != '' OR l.ContactLoginNameTx IS NOT NULL)
)
-- check if a contact is new
IF (@ContactID IS NULL)
BEGIN
-- -----------------------------------------------------
-- MyDB.dbo.Contact
-- -----------------------------------------------------
INSERT INTO [MyDB].[dbo].[Contact]
(
[ContactFirstNameTx],
[ContactLastNameTx],
[ContactEmailTx],
[ContactTitleTx],
[ContactCreateDt],
[ContactCreateByID],
[ContactCreateLoginTypeID]
)
SELECT
[FirstName],
[LastName],
[Email],
[Title],
--[AccountId],
[UserCreatedDate],
@SysId AS [ContactCreateByID], -- as as flag for import [ContactCreateLoginTypeID]
-10 AS [ContactCreateLoginTypeID]
FROM #T o WITH (NOLOCK)
WHERE UserID = @UserID
SET @ContactID = SCOPE_IDENTITY()
END
-- select * from [MyDB].[dbo].[Contact] where contactid in ( 1051364, 466440, 560466, 618576)
-- -----------------------------------------------------
--MyDB.dbo.Login
-- -----------------------------------------------------
INSERT INTO [MyDB].[dbo].[Login]
(
ContactID,
[ContactLoginNameTx], -- V.C. this is email. the field is has a unique constraint
[ContactLoginActiveIn],
[ContactLoginCreateDt],
[ContactLoginCreateByID],
[ContactLoginCreateLoginTypeID],
[ContactLoginPasswordLastChangeDt],
[ContactLoginLastLoginDt],
[ContactLoginPasswordExclusionIn] -- this is not nullable and BIT
)
SELECT
@ContactID AS ContactID,
[Email],
CASE WHEN [UserStatusID] IN (1, 3) THEN 1 ELSE 0 END AS [ContactLoginActiveIn], -- v.c. in Onvia 1 is Active, 2 - Inactive, 3 - Invited, 4 - Inactive By System, 5 - Pending Registration
[UserCreatedDate],
@SysId AS ContactLoginCreateByID, -- as as flag for Onvia import[ContactCreateLoginTypeID]
-10 AS [ContactLoginCreateLoginTypeID],
[UserLastPasswordChangedDate],
[UserLastLoginDate],
0 as ContactLoginPasswordExclusionIn -- this is not nullable and BIT
FROM #T o WITH (NOLOCK)
WHERE UserID = @UserID
AND NOT EXISTS (select * from MyDB.dbo.Login z where z.ContactID = @ContactID)
-- -----------------------------------------------------
-- Activate any inactive users
-- -----------------------------------------------------
IF EXISTS (select * from MyDB.dbo.Login where ContactLoginActiveIn = 0 and ContactID = @ContactID)
AND NOT EXISTS (select * from MyDB.dbo.Login where ContactLoginActiveIn = 1 and ContactID = @ContactID)
BEGIN
UPDATE [MyDB].[dbo].[Login]
SET ContactLoginNameTx = @Email,
ContactLoginActiveIn = (select case when [UserStatusID] IN (1, 3) then 1 else 0 end
from #T o where o.UserID = @UserID)
WHERE ContactID = @ContactID
END
-- -----------------------------------------------------
--MyDB.dbo.ContactPhoneNumber
-- -----------------------------------------------------
INSERT INTO [MyDB].[dbo].[PhoneNumber]
(
ContactID,
PhoneNumberTypeID,
ContactPhoneNumberValueTx
)
SELECT
@ContactID AS ContactID,
1 AS PhoneNumberTypeID,
ISNULL([PhoneNumber1],'') AS ContactPhoneNumberValueTx
FROM #T o WITH (NOLOCK)
WHERE o.UserID = @UserID
AND NOT EXISTS (select * from [MyDB].[dbo].[PhoneNumber] z where z.ContactID = @ContactID);
-- -----------------------------------------------------
--MyDB.dbo.ContactOrg
-- -----------------------------------------------------
INSERT INTO [MyDB].[dbo].[Org]
(
ContactID,
OrgID
)
SELECT
@ContactID AS ContactID,
b.OrgID AS OrgID
FROM #T o WITH (NOLOCK)
INNER JOIN ImportQueue.dbo.OnviaAccountIDOrgIDMigrationLog b WITH (NOLOCK) ON o.AccountId = b.OnviaAccountID
WHERE o.UserID = @UserID
AND NOT EXISTS (select * from [MyDB].[dbo].[Org] z where z.ContactID = @ContactID);
-- -----------------------------------------------------
---- MyDB.dbo.UserIDContactIDMigrationLog
---- log output for new users
-- -----------------------------------------------------
INSERT INTO MyDB.dbo.UserIDContactIDMigrationLog
(
UserID,
ContactId,
CreatedDt,
UpdatedFlag
)
VALUES
(
@UserID,
@ContactID,
GETDATE(),
0
)
-- -----------------------------------------------------
-- EXISTING MATCHED USERS
-- -----------------------------------------------------
-- check if a contact is existing
IF (@ContactID IS NOT NULL)
BEGIN
-- -----------------------------------------------------
-- MyDB.dbo.Contact
-- -----------------------------------------------------
UPDATE MyDB.dbo.Contact
SET ContactFirstNameTx =
CASE
WHEN ContactFirstNameTx NOT IN ('', NULL) THEN c.FirstName
ELSE ContactFirstNameTx
END,
ContactLastNameTx =
CASE
WHEN ContactLastNameTx IN ('', NULL) THEN c.LastName
ELSE ContactLastNameTx
END,
ContactEmailTx =
CASE
WHEN ContactEmailTx IN ('', NULL) THEN c.Email
ELSE ContactEmailTx
END,
ContactTitleTx =
CASE
WHEN ContactTitleTx IN ('', NULL) THEN c.Title
ELSE ContactTitleTx
END,
[ContactCreateDt] =
CASE
WHEN ContactCreateDt IN ('', NULL) THEN c.UserCreatedDate
ELSE ContactCreateDt
END,
[ContactModifyByID] = @SysId,
[ContactModifyLoginTypeID] = - 10
FROM #T c
WHERE UserID = @UserID
AND ContactID = @ContactID;
-- -----------------------------------------------------
---- MyDB.dbo.UserIDContactIDMigrationLog
---- log output for existing matched users
-- -----------------------------------------------------
INSERT INTO MyDB.dbo.UserIDContactIDMigrationLog
(
UserID,
ContactId,
UpdatedDt,
UpdatedFlag
)
VALUES
(
@UserID,
@ContactID,
GETDATE(),
1
);
FETCH NEXT FROM user_cursor INTO @UserID, @Email
-- end cursor loop
END
END
CLOSE user_cursor
DEALLOCATE user_cursor
GO
Instead of Cursors try the below code.
- exec dbo.sp_UserMigration_Users_Copy
DROP PROCEDURE IF EXISTS dbo.sp_UserMigration_Users_Copy
GO
;USE [MyDB];
GO
CREATE PROCEDURE dbo.sp_UserMigration_Users_Copy
@UserID UNIQUEIDENTIFIER = NULL
--,@ContactID INT
AS
DECLARE @Email nvarchar(100),
@ContactID INT;
DECLARE @SysID INT
SET @SysId = 17511;
CREATE TABLE #T
(
ID INT IDENTITY(1,1) NOT NULL,
UserID NVARCHAR(50)NOT NULL,
Email NVARCHAR(50) NOT NULL
);
INSERT INTO #T
SELECT *
FROM MyDB.dbo.User
WHERE UserID IN (
'0604C514',
'C1FDAF34',
'23BABE2D',
'EBA21D10'
);
IF NOT EXISTS (select * from MyDB.sys.tables where name = N'UserIDContactIDMigrationLog')
CREATE TABLE MyDB.dbo.UserIDContactIDMigrationLog
(
UserID UNIQUEIDENTIFIER /* CONSTRAINT [PK_UserIDContactIDMigrationLog_UserID] PRIMARY KEY */ NOT NULL, -- somehow inserts duplicate UserIDs
ContactId INT /*UNIQUE CONSTRAINT [UNIQUE_ContactId] */ NOT NULL,
CreatedDt DATETIME2 NULL,
UpdatedDt DATETIME2 NULL,
UpdatedFlag BIT /*CONSTRAINT [DF_MigratedFlag] DEFAULT(0) */ NULL
);
DECLARE @COUNT INT
DECLARE @I INT = 1
DECLARE @UserID NVARCHAR(50) ;
DECLARE @Email NVARCHAR(50)
SELECT @COUNT = COUNT(*) FROM #T
WHILE(@I <= @COUNT)
BEGIN
SELECT @UserID = UserID, @Email= LTRIM(RTRIM(Email)) FROM #T WHERE ID = @I
SELECT @ContactID = (
SELECT c.ContactId
FROM MyDB.dbo.Contact c
LEFT OUTER JOIN MyDB.dbo.Login l ON c.ContactId = l.ContactId
WHERE (LTRIM(RTRIM(c.ContactEmailTx)) = @Email
OR LTRIM(RTRIM(l.ContactLoginNameTx)) = @Email
--OR l.ContactLoginNameTx LIKE @Email+'%' -- this concept does not work in case of leading space at the ContactLoginNameTx so need to use the code below
OR SUBSTRING(LTRIM(RTRIM(l.ContactLoginNameTx)), -1, CHARINDEX('.INACTIVE.', l.ContactLoginNameTx)) = @Email)
AND (c.ContactEmailTx != '' OR c.ContactEmailTx IS NOT NULL OR l.ContactLoginNameTx != '' OR l.ContactLoginNameTx IS NOT NULL)
)
-- check if a contact is new
IF (@ContactID IS NULL)
BEGIN
-- -----------------------------------------------------
-- MyDB.dbo.Contact
-- -----------------------------------------------------
INSERT INTO [MyDB].[dbo].[Contact]
(
[ContactFirstNameTx],
[ContactLastNameTx],
[ContactEmailTx],
[ContactTitleTx],
[ContactCreateDt],
[ContactCreateByID],
[ContactCreateLoginTypeID]
)
SELECT
[FirstName],
[LastName],
[Email],
[Title],
--[AccountId],
[UserCreatedDate],
@SysId AS [ContactCreateByID], -- as as flag for Onvia import[ContactCreateLoginTypeID]
-10 AS [ContactCreateLoginTypeID]
FROM #T o WITH (NOLOCK)
WHERE UserID = @UserID
SET @ContactID = SCOPE_IDENTITY()
END
-- select * from [MyDB].[dbo].[Contact] where contactid in ( 1051364, 466440, 560466, 618576)
-- -----------------------------------------------------
--MyDB.dbo.Login
-- -----------------------------------------------------
INSERT INTO [MyDB].[dbo].[Login]
(
ContactID,
[ContactLoginNameTx], -- V.C. this is email. the field is has a unique constraint
[ContactLoginActiveIn],
[ContactLoginCreateDt],
[ContactLoginCreateByID],
[ContactLoginCreateLoginTypeID],
[ContactLoginPasswordLastChangeDt],
[ContactLoginLastLoginDt],
[ContactLoginPasswordExclusionIn] -- this is not nullable and BIT
)
SELECT
@ContactID AS ContactID,
[Email],
CASE WHEN [UserStatusID] IN (1, 3) THEN 1 ELSE 0 END AS [ContactLoginActiveIn], -- v.c. in Onvia 1 is Active, 2 - Inactive, 3 - Invited, 4 - Inactive By System, 5 - Pending Registration
[UserCreatedDate],
@SysId AS ContactLoginCreateByID, -- as as flag for Onvia import[ContactCreateLoginTypeID]
-10 AS [ContactLoginCreateLoginTypeID],
[UserLastPasswordChangedDate],
[UserLastLoginDate],
0 as ContactLoginPasswordExclusionIn -- this is not nullable and BIT
FROM #T o WITH (NOLOCK)
WHERE UserID = @UserID
AND NOT EXISTS (select * from MyDB.dbo.Login z where z.ContactID = @ContactID)
-- -----------------------------------------------------
-- Activate any inactive users
-- -----------------------------------------------------
IF EXISTS (select * from MyDB.dbo.Login where ContactLoginActiveIn = 0 and ContactID = @ContactID)
AND NOT EXISTS (select * from MyDB.dbo.Login where ContactLoginActiveIn = 1 and ContactID = @ContactID)
BEGIN
UPDATE [MyDB].[dbo].[Login]
SET ContactLoginNameTx = @Email,
ContactLoginActiveIn = (select case when [UserStatusID] IN (1, 3) then 1 else 0 end
from #T o where o.UserID = @UserID)
WHERE ContactID = @ContactID
END
-- -----------------------------------------------------
--MyDB.dbo.ContactPhoneNumber
-- -----------------------------------------------------
INSERT INTO [MyDB].[dbo].[PhoneNumber]
(
ContactID,
PhoneNumberTypeID,
ContactPhoneNumberValueTx
)
SELECT
@ContactID AS ContactID,
1 AS PhoneNumberTypeID,
ISNULL([PhoneNumber1],'') AS ContactPhoneNumberValueTx
FROM #T o WITH (NOLOCK)
WHERE o.UserID = @UserID
AND NOT EXISTS (select * from [MyDB].[dbo].[PhoneNumber] z where z.ContactID = @ContactID);
-- -----------------------------------------------------
--MyDB.dbo.ContactOrg
-- -----------------------------------------------------
INSERT INTO [MyDB].[dbo].[Org]
(
ContactID,
OrgID
)
SELECT
@ContactID AS ContactID,
b.OrgID AS OrgID
FROM #T o WITH (NOLOCK)
INNER JOIN ImportQueue.dbo.OnviaAccountIDOrgIDMigrationLog b WITH (NOLOCK) ON o.AccountId = b.OnviaAccountID
WHERE o.UserID = @UserID
AND NOT EXISTS (select * from [MyDB].[dbo].[Org] z where z.ContactID = @ContactID);
-- -----------------------------------------------------
---- MyDB.dbo.UserIDContactIDMigrationLog
---- log output for new users
-- -----------------------------------------------------
INSERT INTO MyDB.dbo.UserIDContactIDMigrationLog
(
UserID,
ContactId,
CreatedDt,
UpdatedFlag
)
VALUES
(
@UserID,
@ContactID,
GETDATE(),
0
)
-- -----------------------------------------------------
-- EXISTING MATCHED USERS
-- -----------------------------------------------------
-- check if a contact is existing
IF (@ContactID IS NOT NULL)
BEGIN
-- -----------------------------------------------------
-- MyDB.dbo.Contact
-- -----------------------------------------------------
UPDATE MyDB.dbo.Contact
SET ContactFirstNameTx =
CASE
WHEN ContactFirstNameTx NOT IN ('', NULL) THEN c.FirstName
ELSE ContactFirstNameTx
END,
ContactLastNameTx =
CASE
WHEN ContactLastNameTx IN ('', NULL) THEN c.LastName
ELSE ContactLastNameTx
END,
ContactEmailTx =
CASE
WHEN ContactEmailTx IN ('', NULL) THEN c.Email
ELSE ContactEmailTx
END,
ContactTitleTx =
CASE
WHEN ContactTitleTx IN ('', NULL) THEN c.Title
ELSE ContactTitleTx
END,
[ContactCreateDt] =
CASE
WHEN ContactCreateDt IN ('', NULL) THEN c.UserCreatedDate
ELSE ContactCreateDt
END,
[ContactModifyByID] = @SysId,
[ContactModifyLoginTypeID] = - 10
FROM #T c
WHERE UserID = @UserID
AND ContactID = @ContactID;
-- -----------------------------------------------------
---- MyDB.dbo.UserIDContactIDMigrationLog
---- log output for existing matched users
-- -----------------------------------------------------
INSERT INTO MyDB.dbo.UserIDContactIDMigrationLog
(
UserID,
ContactId,
UpdatedDt,
UpdatedFlag
)
VALUES
(
@UserID,
@ContactID,
GETDATE(),
1
);
END
SET @I = @I+1;
END