We help IT Professionals succeed at work.

Append tables to create unique dataset

markej
markej asked
on
157 Views
Last Modified: 2018-08-22
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.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

Open in new window

Comment
Watch Question

Senior Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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?


Mark
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.