i want to import customers csv list in nop 3.7 but no way?
plz help?
WITH
BinarySaltyUsers AS (
SELECT NewId() AS CustomerGuid
,MyOldCustomersTable.Email
,CAST(RTRIM(MyOldCustomersTable.[Password]) AS varchar(201)) AS [Password]
,CONVERT(varbinary(max), CAST((ABS(CHECKSUM(NewId())) % 90000)+ 9999 AS varchar(100))) AS Salt
FROM MyOldCustomersTable
),
Base64SaltyUsers AS (
SELECT CustomerGuid
,[Password]
,cast('' as xml).value('xs:base64Binary(sql:column("Salt"))', 'varchar(max)') AS PasswordSalt
FROM BinarySaltyUsers
),
PasswordUsers AS (
SELECT CustomerGuid
,PasswordSalt
,[Password]
,UPPER(master.dbo.fn_varbintohexsubstring(0, HashBytes('SHA1', [Password]+PasswordSalt),1,0)) AS HashedPassword
FROM Base64SaltyUsers
)
MERGE AshNop.dbo.Customer AS mTarget
USING (
SELECT CustomerGuid
,PasswordSalt
,[Password]
,HashedPassword
FROM PasswordUsers) AS mSource
ON (mSource.Email = mTarget.Email
)
WHEN NOT MATCHED BY TARGET
THEN INSERT
(CustomerGuid
,Username
,[Password]
,PasswordFormatId
,PasswordSalt
,AdminComment
,IsTaxExempt
,AffiliateId
,VendorId
,HasShoppingCartItems
,Active
,Deleted
,IsSystemAccount
--,SystemName
--,LastIpAddress
,CreatedOnUtc
--,LastLoginDateUtc
,LastActivityDateUtc
--,BillingAddress_Id
--,ShippingAddress_Id
)
VALUES (CustomerGuid
,Email --Username
,HashedPassword
,1 --PasswordFormatId
,PasswordSalt --PasswordSalt
--,AdminComment
,0 --<IsTaxExempt, bit,>
,0 --<AffiliateId, int,>
,0 --<VendorId, int,>
,0 --<HasShoppingCartItems, bit,>
,1 --Active
,0 --<Deleted, bit,>
,0 --<IsSystemAccount, bit,>
--,<SystemName, nvarchar(max),>
--,<LastIpAddress, nvarchar(max),>
,GETUTCDATE() --<CreatedOnUtc, datetime,>
--,<LastLoginDateUtc, datetime,>
,GETUTCDATE() --<LastActivityDateUtc, datetime,>
--,<BillingAddress_Id, int,>
--,<ShippingAddress_Id, int,>
)
OUTPUT $action, inserted.*;