How to analyze and resolve the migration script error "Subquery returned more than 1 value"?

support
1

Error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Script:

INSERT INTO data.dbo.OrigamUser
(
    refBusinessPartnerId
    , Password
    , RecordCreated
    , RecordUpdated
    , Id
    , IsLockedOut
    , LastLoginDate
    , LastLockoutDate
    , FailedPasswordAttemptCount
    , FailedPasswordAttemptWindowStart
    , UserName
    , EmailConfirmed
)
SELECT
    (SELECT Id From data.dbo.BusinessPartner bp WHERE bp.UserName = au.UserName )
    , {fn CONCAT({fn CONCAT(am.Password  ,  '|' )} , am.PasswordSalt)} 
    , am.CreateDate
    , NULL
    , NEWID()
    , am.IsLockedOut
    , am.LastLoginDate
    , am.LastLockoutDate
    , am.FailedPasswordAttemptCount
    , am.FailedPasswordAnswerAttemptWindowStart
    , au.UserName
    , am.IsApproved
FROM
    aspnetdb.dbo.vw_aspnet_Users au
    INNER JOIN aspnetdb.dbo.aspnet_Membership am ON am.UserId = au.UserId
WHERE
    (SELECT Id From data.dbo.BusinessPartner bp WHERE bp.UserName = au.UserName ) is not null
2

Looks like you have 2 records with the same UserName in your BusinessPartner table.

You can check which by using the following SQL statement:

SELECT UserName, Count(*)
FROM BusinessPartner
GROUP BY UserName
HAVING Count(*) > 1