I need to import a lot of legacy users into a MojoPortal website. This is how I tackled the problem.
I created a stored procedure to bulk insert the data into mp_Users and mp_UserProperties tables. Then I wrote a cursor script to loop through the records of a legacy table I copied over to the MojoPortal database. The cursor calls the stored procedure and inserts the proper data for each user record.
The cursor script and stored procedure are below for anyone who can make use of them. The script will have to to be modified to match your data source and the stored procedure has extra variables that match my custom profile properties (configured in /CustomProfile.config)
No warranty is implied or granted but I tried to add in comments to help you make the appropriate changes. So far I tested to make sure you could access the new user profiles in the Administration->Members List
If there is a more appropriate place to post utility code please let me know.
Good Luck,
Mike
-------------------------------------START CURSOR SCRIPT---------------------------------------------------------------
DECLARE MY_CURSOR Cursor --- NAME IT
FOR -- GET Data from legacy table
SELECT [user_id]
,[fname]
,[lname]
,[addr1]
,[addr2]
,[city]
,[state]
,[zip]
,[country]
,[email]
,[password]
,[user_level]
,[user_status]
,[newsletter]
,[newsletter_html]
,[phone]
,[fax]
,[payment_type]
,[subscription_date]
,[subscription_terms]
,[agent_id]
,[company]
,[accept_agreement]
,[Notes]
,[Promo]
FROM [dbo].[users] --WHERE [user_id] = 367
--Open the cursor
Open My_Cursor --- (remember to CLOSE IT LATER)
--- We need to make containers for the Cursor Info
DECLARE --Declare Variables to hold column data
@user_id [int] ,
@fname [varchar](50) ,
@lname [varchar](50) ,
@addr1 [varchar](50) ,
@addr2 [varchar](50) ,
@city [varchar](50) ,
@state [varchar](50) ,
@zip [varchar](15) ,
@country [varchar](50) ,
@email [varchar](100) ,
@password [varchar](100) ,
@user_level [char](3) ,
@user_status [bit] ,
@newsletter [bit] ,
@newsletter_html [bit] ,
@phone [varchar](50) ,
@fax [varchar](50) ,
@payment_type [varchar](50) ,
@subscription_date [datetime] ,
@subscription_terms [varchar](255) ,
@agent_id [int] ,
@company [varchar](100) ,
@accept_agreement [int] ,
@Notes [varchar](2048) ,
@Promo [varchar](50),
@FullName [varchar](100),
@Date [varchar](100)
--Try to get first row of data (if it exists)
FETCH NEXT FROM MY_Cursor INTO --(# of varaibles must equal the # of columns in your select statement)
@user_id, @fname, @lname, @addr1, @addr2, @city, @state, @zip, @country, @email, @password, @user_level, @user_status,
@newsletter, @newsletter_html, @phone, @fax, @payment_type, @subscription_date, @subscription_terms, @agent_id,
@company, @accept_agreement, @Notes, @Promo
--while there are rows i.e. no erros from tryig to fetch a row that isn't there
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
--- Here is where we put the fun stuff!!!
-- TEST CODE : This will display the the first row an on and on
--Select @user_id, @fname, @lname, @addr1, @addr2, @city, @state, @zip, @country, @email, @password, @user_level, @user_status, @newsletter, @newsletter_html,
--@phone, @fax, @payment_type, @subscription_date, @subscription_terms, @agent_id, @company, @accept_agreement, @Notes, @Promo
---Call stored proc to insert new user into Mojo Portal tables
DECLARE @RC int, @SITEID int, @SITEGUID uniqueidentifier
SET @SITEID = 1
SET @SITEGUID = (SELECT TOP 1 [SiteGuid] FROM [dbo].[mp_Sites] WHERE [SiteID] = @SITEID)
SET @FullName = @fname + ' ' + @LName
IF(@subscription_date IS NULL )
SET @Date = '1/1/1900'
ELSE
SET @Date = @subscription_date
EXECUTE @RC = [p_Add_User]
--@UserID OUTPUT
@SiteID = @SITEID
,@Name = @FullName
,@LoginName = @email
,@Email = @email
--,@LoweredEmail
--,@PasswordQuestion
--,@PasswordAnswer
--,@Gender
--,@ProfileApproved
--,@RegisterConfirmGuid
--,@ApprovedForForums
--,@Trusted
--,@DisplayInMemberList
--,@WebSiteURL
--,@Country
,@State = @state
--,@Occupation
--,@Interests
--,@MSN
--,@Yahoo
--,@AIM
--,@ICQ
--,@TotalPosts
--,@AvatarUrl
--,@TimeOffsetHours
--,@Signature
--,@DateCreated
--,@UserGuid OUTPUT
--,@Skin
--,@IsDeleted
--,@LastActivityDate
--,@LastLoginDate
--,@LastPasswordChangedDate
--,@LastLockoutDate
--,@FailedPasswordAttemptCount
--,@FailedPwdAttemptWindowStart
--,@FailedPwdAnswerAttemptCount
--,@FailedPwdAnswerWindowStart
--,@IsLockedOut
--,@MobilePIN
--,@PasswordSalt
--,@Comment
--,@OpenIDURI
--,@WindowsLiveID
,@SiteGuid = @SITEGUID
--,@TotalRevenue
,@FirstName = @fname
,@LastName = @lname
,@Pwd = @password
--,@MustChangePwd
--,@NewEmail
--,@EditorPreference
--,@EmailChangeGuid
--,@TimeZoneId
--,@PasswordResetGuid
--,@Company
,@Address1 = @addr1
,@Address2 = @addr2
,@City = @city
,@Zip = @zip
,@Phone = @phone
,@Fax = @fax
,@PromotionalCode = @Promo
,@Newsletter = @newsletter
,@HTMLEmail = @newsletter_html
,@SignMeUp = @accept_agreement
,@TermsAndConditions = @accept_agreement
,@CPEID = @user_id
,@UserLevel = @user_level
,@PaymentType = @payment_type
,@SubscriptionDate = @Date
,@SubscriptionTerms = @subscription_terms
,@AgentID = @agent_id
--Try to get next row of data
FETCH NEXT FROM MY_Cursor INTO
@user_id, @fname, @lname, @addr1, @addr2, @city, @state, @zip, @country, @email, @password, @user_level, @user_status,
@newsletter, @newsletter_html, @phone, @fax, @payment_type, @subscription_date, @subscription_terms, @agent_id,
@company, @accept_agreement, @Notes, @Promo
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
-------------------------------------START STORED PROC-----------------------------------------------------------------
/****** Object: StoredProcedure [dbo].[p_Add_User] Script Date: 12/13/2011 18:53:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [p_Add_User]
(
@UserID [int] = NULL out --RETURN VALUE
,@SiteID [int] --REQUIRED VALUE
,@Name [nvarchar](100) --REQUIRED VALUE
,@LoginName [nvarchar](50) --REQUIRED VALUE
,@Email [nvarchar](100) --REQUIRED VALUE
,@LoweredEmail [nvarchar](100) = null
,@PasswordQuestion [nvarchar](255) = null
,@PasswordAnswer [nvarchar](255) = null
,@Gender [nchar](10) = null
,@ProfileApproved [bit] = 1
,@RegisterConfirmGuid [uniqueidentifier] = null
,@ApprovedForForums [bit] = 1
,@Trusted [bit] = 0
,@DisplayInMemberList [bit] = 1
,@WebSiteURL [nvarchar](100) = null
,@Country [nvarchar](100) = 'USA'
,@State [nvarchar](100) = null
,@Occupation [nvarchar](100) = null
,@Interests [nvarchar](100) = null
,@MSN [nvarchar](50) = null
,@Yahoo [nvarchar](50) = null
,@AIM [nvarchar](50) = null
,@ICQ [nvarchar](50) = null
,@TotalPosts [int] = 0
,@AvatarUrl [nvarchar](255) = 'blank.gif'
,@TimeOffsetHours [int] = 0
,@Signature [nvarchar](max) = null
,@DateCreated [datetime] = null
,@UserGuid [uniqueidentifier] = NULL out --RETURN VALUE
,@Skin [nvarchar](100) = null
,@IsDeleted [bit] = 0
,@LastActivityDate [datetime] = null
,@LastLoginDate [datetime] = null
,@LastPasswordChangedDate [datetime] = null
,@LastLockoutDate [datetime] = null
,@FailedPasswordAttemptCount [int] = null
,@FailedPwdAttemptWindowStart [datetime] = null
,@FailedPwdAnswerAttemptCount [int] = null
,@FailedPwdAnswerWindowStart [datetime] = null
,@IsLockedOut [bit] = 0
,@MobilePIN [nvarchar](16) = null
,@PasswordSalt [nvarchar](128) = null
,@Comment [nvarchar](max) = null
,@OpenIDURI [nvarchar](255) = null
,@WindowsLiveID [nvarchar](36) = null
,@SiteGuid [uniqueidentifier] = null
,@TotalRevenue [decimal](15,4) = null
,@FirstName [nvarchar](100) = null
,@LastName [nvarchar](100) = null
,@Pwd [nvarchar](1000) --REQUIRED VALUE
,@MustChangePwd [bit] = 0
,@NewEmail [nvarchar](100) = null
,@EditorPreference [nvarchar](100) = null
,@EmailChangeGuid [uniqueidentifier] = '00000000-0000-0000-0000-000000000000'
,@TimeZoneId [nvarchar](32) = 'Eastern Standard Time'
,@PasswordResetGuid [uniqueidentifier] = '00000000-0000-0000-0000-000000000000'
--After this line are the user configured variables in the /CustomProfile.config file
--These are the one's I used. Yours will be different
,@Company [nvarchar](100) = null
,@Address1 [nvarchar](100) = null
,@Address2 [nvarchar](100) = null
,@City [nvarchar](100) = null
,@Zip [nvarchar](100) = null
,@Phone [nvarchar](100) = null
,@Fax [nvarchar](100) = null
,@PromotionalCode [nvarchar](100) = null
,@Newsletter [bit] = null
,@HTMLEmail [bit] = null
,@SignMeUp [bit] = null
,@TermsAndConditions [bit] = null
,@CPEID [nvarchar](100) = null
,@UserLevel [nvarchar](100) = null
,@PaymentType [nvarchar](100) = null
,@SubscriptionDate [nvarchar](100) = null
,@SubscriptionTerms [nvarchar](100) = null
,@AgentID [nvarchar](100) = null
,@AcceptAgreement [nvarchar](100) = null
)
--WITH ENCRYPTION
AS
Declare @NEWID as uniqueidentifier
SET @NEWID = NEWID()
IF @DateCreated IS NULL SET @DateCreated = GETDATE()
INSERT INTO [dbo].[mp_Users]
(
[SiteID]
,[Name]
,[LoginName]
,[Email]
,[LoweredEmail]
,[PasswordQuestion]
,[PasswordAnswer]
,[Gender]
,[ProfileApproved]
,[RegisterConfirmGuid]
,[ApprovedForForums]
,[Trusted]
,[DisplayInMemberList]
,[WebSiteURL]
,[Country]
,[State]
,[Occupation]
,[Interests]
,[MSN]
,[Yahoo]
,[AIM]
,[ICQ]
,[TotalPosts]
,[AvatarUrl]
,[TimeOffsetHours]
,[Signature]
,[DateCreated]
,[UserGuid]
,[Skin]
,[IsDeleted]
,[LastActivityDate]
,[LastLoginDate]
,[LastPasswordChangedDate]
,[LastLockoutDate]
,[FailedPasswordAttemptCount]
,[FailedPwdAttemptWindowStart]
,[FailedPwdAnswerAttemptCount]
,[FailedPwdAnswerWindowStart]
,[IsLockedOut]
,[MobilePIN]
,[PasswordSalt]
,[Comment]
,[OpenIDURI]
,[WindowsLiveID]
,[SiteGuid]
,[TotalRevenue]
,[FirstName]
,[LastName]
,[Pwd]
,[MustChangePwd]
,[NewEmail]
,[EditorPreference]
,[EmailChangeGuid]
,[TimeZoneId]
,[PasswordResetGuid])
VALUES
(@SiteID,
@Name,
@LoginName,
@Email,
@LoweredEmail,
@PasswordQuestion,
@PasswordAnswer,
@Gender,
@ProfileApproved,
@RegisterConfirmGuid,
@ApprovedForForums,
@Trusted,
@DisplayInMemberList,
@WebSiteURL,
@Country,
@State,
@Occupation,
@Interests,
@MSN,
@Yahoo,
@AIM,
@ICQ,
@TotalPosts,
@AvatarUrl,
@TimeOffsetHours,
@Signature,
@DateCreated,
@NEWID,
@Skin,
@IsDeleted,
@LastActivityDate,
@LastLoginDate,
@LastPasswordChangedDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPwdAttemptWindowStart,
@FailedPwdAnswerAttemptCount,
@FailedPwdAnswerWindowStart,
@IsLockedOut,
@MobilePIN,
@PasswordSalt,
@Comment,
@OpenIDURI,
@WindowsLiveID,
@SiteGuid,
@TotalRevenue,
@FirstName,
@LastName,
@Pwd,
@MustChangePwd,
@NewEmail,
@EditorPreference,
@EmailChangeGuid,
@TimeZoneId,
@PasswordResetGuid
)
--Get New User's ID
Set @UserID = SCOPE_IDENTITY()
SELECT @UserGuid = u.[UserGuid] FROM [mp_Users] u WHERE (u.[UserID] = @UserID)
--Insert User Defined Properties NON BOOLEAN
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'Company',@Company,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'Address1',@Address1,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'Address2',@Address2,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'City',@City,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'Zip',@Zip,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'Phone',@Phone,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'Fax',@Fax,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'PromotionalCode',@PromotionalCode,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'CPEID',@CPEID,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'UserLevel',@UserLevel,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'PaymentType',@PaymentType,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'SubscriptionDate',@SubscriptionDate,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'SubscriptionTerms',@SubscriptionTerms,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'AgentID',@AgentID,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueString],[IsLazyLoaded])
VALUES(@UserGuid ,'AcceptAgreement',@AcceptAgreement,0)
--Insert User Defined Properties BOOLEAN
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueBinary],[IsLazyLoaded])
VALUES(@UserGuid ,'Newsletter',@Newsletter,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueBinary],[IsLazyLoaded])
VALUES(@UserGuid ,'HTMLEmail',@HTMLEmail,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueBinary],[IsLazyLoaded])
VALUES(@UserGuid ,'SignMeUp',@SignMeUp,0)
INSERT INTO [mp_UserProperties]
([UserGuid],[PropertyName],[PropertyValueBinary],[IsLazyLoaded])
VALUES(@UserGuid ,'TermsAndConditions',@TermsAndConditions,0)
--Return Error code
RETURN (@@ERROR)
GO