User Migration Script

UserMigration.txt

-----------------------------------------------------------------------------------------------------------------
INSERT INTO dbo.aspnet_Membership
SELECT [ApplicationId],
	[UserId],
	[Password],
	[PasswordFormat],
	[PasswordSalt],
	[MobilePIN],
	[Email],
	[LoweredEmail],
	[PasswordQuestion],
	[PasswordAnswer],
	[IsApproved],
	[IsLockedOut],
	[CreateDate],
	[LastLoginDate],
	[LastPasswordChangedDate],
	[LastLockoutDate],
	[FailedPasswordAttemptCount],
	[FailedPasswordAttemptWindowStart],
	[FailedPasswordAnswerAttemptCount],
	[FailedPasswordAnswerAttemptWindowStart],
	CONVERT(nvarchar,[Comment])
	FROM [SugSession_Core_Source_DB].dbo.aspnet_Membership
WHERE NOT EXISTS
    (SELECT *
     FROM dbo.aspnet_Membership a
	 INNER JOIN [SugSession_Core_Source_DB].dbo.aspnet_Membership b
	 ON a.UserID = b.UserID)
-----------------------------------------------------------------------------------------------------------------
INSERT INTO dbo.aspnet_Paths
SELECT * FROM [SugSession_Core_Source_DB].dbo.aspnet_Paths
WHERE NOT EXISTS
    (SELECT *
     FROM dbo.aspnet_Paths 
     WHERE dbo.aspnet_Paths.PathId
     = [SugSession_Core_Source_DB].dbo.aspnet_Paths.PathId)
-----------------------------------------------------------------------------------------------------------------
INSERT INTO dbo.aspnet_PersonalizationAllUsers
SELECT * FROM [SugSession_Core_Source_DB].dbo.aspnet_PersonalizationAllUsers
WHERE NOT EXISTS
    (SELECT *
     FROM dbo.aspnet_PersonalizationAllUsers 
     WHERE dbo.aspnet_PersonalizationAllUsers.PathId
     = [SugSession_Core_Source_DB].dbo.aspnet_PersonalizationAllUsers.PathId)
-----------------------------------------------------------------------------------------------------------------
INSERT INTO dbo.aspnet_PersonalizationPerUser
SELECT * FROM [SugSession_Core_Source_DB].dbo.aspnet_PersonalizationPerUser
WHERE NOT EXISTS
    (SELECT *
     FROM dbo.aspnet_PersonalizationPerUser 
     WHERE dbo.aspnet_PersonalizationPerUser.PathId
     = [SugSession_Core_Source_DB].dbo.aspnet_PersonalizationPerUser.PathId)
-----------------------------------------------------------------------------------------------------------------
INSERT INTO dbo.aspnet_Profile
SELECT [UserId] as UserId,
	CONVERT(nvarchar,[PropertyNames]),
	CONVERT(nvarchar,[PropertyValuesString]),
	[PropertyValuesBinary],
	[LastUpdatedDate] FROM [SugSession_Core_Source_DB].dbo.aspnet_Profile
WHERE NOT EXISTS
    (SELECT *
     FROM dbo.aspnet_Profile 
     WHERE dbo.aspnet_Profile.UserId IN(SELECT UserID FROM [SugSession_Core_Source_DB].dbo.aspnet_Profile))
-----------------------------------------------------------------------------------------------------------------       
INSERT INTO dbo.aspnet_Roles
SELECT * FROM [SugSession_Core_Source_DB].dbo.aspnet_Roles
WHERE NOT EXISTS
    (SELECT *
     FROM dbo.aspnet_Roles 
     WHERE dbo.aspnet_Roles.RoleName IN (SELECT RoleName FROM [SugSession_Core_Source_DB].dbo.aspnet_Roles))
-----------------------------------------------------------------------------------------------------------------        
INSERT INTO dbo.aspnet_SchemaVersions
SELECT * FROM [SugSession_Core_Source_DB].dbo.aspnet_SchemaVersions
WHERE NOT EXISTS
    (SELECT *
     FROM dbo.aspnet_SchemaVersions 
     WHERE feature IN (SELECT Feature FROM [SugSession_Core_Source_DB].dbo.aspnet_SchemaVersions))
-----------------------------------------------------------------------------------------------------------------   
INSERT INTO dbo.aspnet_Users
SELECT * FROM [SugSession_Core_Source_DB].dbo.aspnet_Users
WHERE NOT EXISTS
    (SELECT dbo.aspnet_Users.UserName
     FROM dbo.aspnet_Users 
     WHERE dbo.aspnet_Users.UserName
     = [SugSession_Core_Source_DB].dbo.aspnet_Users.UserName)
-----------------------------------------------------------------------------------------------------------------
INSERT INTO dbo.aspnet_UsersInRoles
SELECT * FROM [SugSession_Core_Source_DB].dbo.aspnet_UsersInRoles
WHERE NOT EXISTS
    (SELECT *
     FROM dbo.aspnet_UsersInRoles 
     WHERE dbo.aspnet_UsersInRoles.UserId
     = [SugSession_Core_Source_DB].dbo.aspnet_UsersInRoles.UserId)
-----------------------------------------------------------------------------------------------------------------
INSERT INTO dbo.aspnet_WebEvent_Events
SELECT * FROM [SugSession_Core_Source_DB].dbo.aspnet_WebEvent_Events
WHERE NOT EXISTS
    (SELECT *
     FROM dbo.aspnet_WebEvent_Events 
     WHERE dbo.aspnet_WebEvent_Events.EventId
     = [SugSession_Core_Source_DB].dbo.aspnet_WebEvent_Events.EventId)
-----------------------------------------------------------------------------------------------------------------

----- THIS IS MOST IMP STEP AS THESE IDs OF SITECORE XP and XM GUIDs, IT WILL BE DIFFERENT IN BOTH - DAIVAGNA --------------
UPDATE [sitecore1031_Core].[dbo].[aspnet_Membership]
SET ApplicationId = 'D0883DF6-CF1F-4EF8-A6D3-9CA826D73C7A'
WHERE ApplicationId = 'BCE2AB59-4983-4019-BE48-0450821711EB';

UPDATE [sitecore1031_Core].[dbo].[aspnet_Users]
SEt ApplicationId = 'D0883DF6-CF1F-4EF8-A6D3-9CA826D73C7A'
WHERE ApplicationId = 'BCE2AB59-4983-4019-BE48-0450821711EB';

UPDATE [sitecore1031_Core].[dbo].[aspnet_Roles]
SET ApplicationId = 'D0883DF6-CF1F-4EF8-A6D3-9CA826D73C7A'
WHERE ApplicationId = 'BCE2AB59-4983-4019-BE48-0450821711EB';

UPDATE [sitecore1031_Core].[dbo].[aspnet_Applications]
SET ApplicationId = 'D0883DF6-CF1F-4EF8-A6D3-9CA826D73C7A'
WHERE ApplicationId = 'BCE2AB59-4983-4019-BE48-0450821711EB';