I need to transfer users info from the live DB to its new home (which was created via a backup of the live DB so Im only looking to port new users across.(running sql sqerver 2005)
I can Transfer the aspnet_ tables without any issue
However when I try to do the users table it errors with:
Msg 512, Level 16, State 1, Procedure Mailing_list, Line 18 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Query
begin TRAN
SET IDENTITY_INSERT TargetDB.dbo.users ON
INSERT INTO TargetDB.dbo.users (UserId, UserName, FirstName, LastName, IsSuperUser,
AffiliateId, Email, DisplayName, UpdatePassword)
(SELECT dnnu.UserId, dnnu.username, dnnu.firstname, dnnu.lastname,
dnnu.isSuperuser, dnnu.AffiliateId, dnnu.Email, dnnu.DisplayName,
dnnu.UpdatePassword
FROM LiveDB.dbo.users dnnu
INNER JOIN LiveDB.dbo.aspnet_users u
ON (dnnu.username = u.username)
WHERE dnnu.username NOT IN (SELECT username
FROM TargetDB.dbo.users))
SET IDENTITY_INSERT TargetDB.dbo.users OFF
commit TRAN
I have also run this same section of code on at least two other Database with no issues
Any help would be greatly appreciated
Thanks in advance Matt
After spending an hour or two yesturday staring at this issue and deciding to ask the question I took a second glance and it hit me.
There was a trigger on the table for insert/update and it was throwing a wobbly dropped it ran the query and it now works