Hi guys, I had some trouble importing users into nopCommerce from a different e-commerce system so I thought I'd share my scripts to help others in future. The following is for nopCommerce 3.90 database schema but I'm sure it could be adapted, and also improved upon.
The script I started with was posted by Pete Mitch
here.
First thing is to get your users into a database table however you can, I imported an Excel spreadsheet into a table called dbo.WebCustomers so you will see this referred to in the upcoming scripts.
--- INSERT CUSTOMERS ---
MERGE dbo.Customer AS mTarget
USING (
SELECT
NewId() AS CustomerGuid
,[EMAIL ADDRESS] AS Email
FROM WebCustomers) AS mSource
ON (mSource.Email = mTarget.Email)
WHEN NOT MATCHED BY TARGET
THEN INSERT
(CustomerGuid
,Username
,Email
,IsTaxExempt
,AffiliateId
,VendorId
,HasShoppingCartItems
,RequireReLogin
,FailedLoginAttempts
,Active
,Deleted
,IsSystemAccount
--,SystemName
--,LastIpAddress
,CreatedOnUtc
--,LastLoginDateUtc
,LastActivityDateUtc
,RegisteredInStoreId
--,BillingAddress_Id
--,ShippingAddress_Id
)
VALUES (CustomerGuid
,Email --Username
,Email
,0 --<IsTaxExempt, bit,>
,0 --<AffiliateId, int,>
,0 --<VendorId, int,>
,0 --<HasShoppingCartItems, bit,>
,0 --<RequireReLogin, bit,>
,0 --<FailedLoginAttempts, int,>
,1 --Active
,0 --<Deleted, bit,>
,0 --<IsSystemAccount, bit,>
--,<SystemName, nvarchar(max),>
--,<LastIpAddress, nvarchar(max),>
,GETUTCDATE() --<CreatedOnUtc, datetime,>
--,<LastLoginDateUtc, datetime,>
,GETUTCDATE() --<LastActivityDateUtc, datetime,>
,1 --<RegisteredInStoreId, int,>
--,<BillingAddress_Id, int,>
--,<ShippingAddress_Id, int,>
)
OUTPUT $action, inserted.*;
--- INSERT CUSTOMER REGISTERED ROLES ---
MERGE dbo.Customer_CustomerRole_Mapping AS mTarget
USING (
SELECT
Customer.Id AS [CustomerId]
,3 AS RoleId --Registered RoleId
FROM WebCustomers
INNER JOIN dbo.Customer ON WebCustomers.[Email Address] = Customer.Username) AS mSource
ON (mSource.[CustomerId] = mTarget.Customer_Id)
WHEN NOT MATCHED BY TARGET
THEN INSERT
(Customer_Id
,CustomerRole_Id
)
VALUES (CustomerId
,RoleId
)
OUTPUT $action, inserted.*;
--- INSERT CUSTOMER PASSWORDS ---
;WITH
BinarySaltyUsers AS (
SELECT WebCustomers.[EMAIL ADDRESS] AS Email
,CAST(RTRIM(WebCustomers.[Password]) AS varchar(201)) AS [Password]
,CONVERT(varbinary(max), CAST((ABS(CHECKSUM(NewId())) % 90000)+ 9999 AS varchar(100))) AS Salt
FROM WebCustomers
),
Base64SaltyUsers AS (
SELECT Email
,[Password]
,cast('' as xml).value('xs:base64Binary(sql:column("Salt"))', 'varchar(max)') AS PasswordSalt
FROM BinarySaltyUsers
),
PasswordUsers AS (
SELECT Email
,PasswordSalt
,[Password]
,UPPER(master.dbo.fn_varbintohexsubstring(0, HashBytes('SHA1', [Password]+PasswordSalt),1,0)) AS HashedPassword
FROM Base64SaltyUsers
)
MERGE dbo.CustomerPassword AS mTarget
USING (
SELECT
Customer.Id AS [CustomerId]
,PasswordUsers.Email AS [Email]
,PasswordSalt AS [PasswordSalt]
,HashedPassword AS [HashedPassword]
FROM PasswordUsers
INNER JOIN dbo.Customer ON PasswordUsers.Email = Customer.Username) AS mSource
ON (mSource.[CustomerId] = mTarget.CustomerId)
WHEN NOT MATCHED BY TARGET
THEN INSERT
(CustomerId
,Password
,PasswordFormatId
,PasswordSalt
,CreatedOnUtc
)
VALUES (CustomerId
,HashedPassword
,1
,PasswordSalt
,GETDATE()
)
OUTPUT $action, inserted.*;
--- INSERT CUSTOMER GENERIC ATTRIBUTE ---
MERGE dbo.[GenericAttribute] AS mTarget
USING (
SELECT
Customer.Id AS [CustomerId]
,'Customer' AS [KeyGroup]
,'Company' AS [Key]
,[COMPANY NAME] AS [Company]
,0 AS [StoreId]
FROM WebCustomers
INNER JOIN dbo.Customer ON WebCustomers.[Email Address] = Customer.Username) AS mSource
ON (mSource.[CustomerId] = mTarget.[EntityId] AND mSource.[Key] = mTarget.[Key] AND mSource.[KeyGroup] = mTarget.[KeyGroup])
WHEN NOT MATCHED BY TARGET
THEN INSERT
([EntityId]
,[KeyGroup]
,[Key]
,[Value]
,[StoreId]
)
VALUES ([CustomerId]
,[KeyGroup]
,[Key]
,[Company]
,[StoreId]
)
OUTPUT $action, inserted.*;