markej
asked on
Append tables to create unique dataset
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
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.usertype
FROM 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm trying to understand how this works so where would be a good place to read up on this?
Mark