I have a stored procedure that includes the TWO select statements shown below, I need to append the data into a single temporary table, removing all duplicates (the unique key is email) HELP how can I do this? I've tried creating a temporary table and using "select into" BUT creates an error in the stored procedure temporary table already exists .
Mark
SELECT DISTINCT dbo.Userinfo.id, dbo.Userinfo.Email, dbo.Userinfo.fname, dbo.Userinfo.lname, dbo.Userinfo.status, dbo.Userinfo.parentid, dbo.Userinfo.usertype FROM dbo.Userinfo LEFT OUTER JOIN dbo.groupInfo ON dbo.Userinfo.ClubId = dbo.groupInfo.clubid AND dbo.Userinfo.id = dbo.groupInfo.userinfo_id LEFT OUTER JOIN dbo.TeamMembers ON dbo.Userinfo.id = dbo.TeamMembers.userinfo_id LEFT OUTER JOIN dbo.UserInfo_Static_Roles ON dbo.Userinfo.id = dbo.UserInfo_Static_Roles.userinfo_Id WHERE dbo.Userinfo.status <> 3 and (dbo.TeamMembers.status is null or dbo.TeamMembers.status <> 3 ) and (dbo.Userinfo.ClubId = @clubid) AND (@roles IS NULL OR dbo.UserInfo_Static_Roles.Static_Roles_id in (select VALUE from dbo.SPLIT( @roles)) ) AND (@status IS NULL OR dbo.Userinfo.status = @status) AND (@teamid IS NULL OR dbo.TeamMembers.Teamid = @teamid) AND (@Groupid is NULL OR dbo.groupinfo.groupid = @Groupid) AND (@name is NULL OR dbo.Userinfo.lname LIKE @name OR dbo.Userinfo.fname LIKE @name OR dbo.Userinfo.Email LIKE @name) ORDER BY dbo.Userinfo.lname, dbo.Userinfo.fname SELECT DISTINCT dbo.Userinfo.id, dbo.Userinfo.Email, dbo.Userinfo.fname, dbo.Userinfo.lname, dbo.Userinfo.status, dbo.Userinfo.parentid, dbo.Userinfo.usertypeFROM dbo.Userinfo INNER JOIN dbo.LnkParent_ChildIDs ON dbo.Userinfo.id = dbo.LnkParent_ChildIDs.ParentUserinfoId INNER JOIN dbo.TeamMembers ON dbo.LnkParent_ChildIDs.ChildUserinfoId = dbo.TeamMembers.userinfo_id LEFT OUTER JOIN dbo.groupInfo ON dbo.Userinfo.ClubId = dbo.groupInfo.clubid AND dbo.Userinfo.id = dbo.groupInfo.userinfo_id LEFT OUTER JOIN dbo.UserInfo_Static_Roles ON dbo.Userinfo.id = dbo.UserInfo_Static_Roles.userinfo_Id WHERE dbo.Userinfo.status <> 3 and (dbo.TeamMembers.status is null or dbo.TeamMembers.status <> 3 ) and (dbo.Userinfo.ClubId = @CLUBID) AND (@roles IS NULL OR dbo.UserInfo_Static_Roles.Static_Roles_id in (select VALUE from dbo.SPLIT( @roles)) ) AND (@status IS NULL OR dbo.Userinfo.status = @status) AND (@teamid IS NULL OR dbo.TeamMembers.Teamid = @teamid) AND (@Groupid is NULL OR dbo.groupinfo.groupid = @Groupid) AND (@name is NULL OR dbo.Userinfo.lname LIKE @name OR dbo.Userinfo.fname LIKE @name OR dbo.Userinfo.Email LIKE @name) ORDER BY dbo.Userinfo.lname, dbo.Userinfo.fname
Thanks, That's seems to almost work. The id column is the primary key so I have replaced the U.Email with U.Id and that returns the correct records without any duplicates.
I'm trying to understand how this works so where would be a good place to read up on this?
I'm trying to understand how this works so where would be a good place to read up on this?
Mark