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

		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

markejAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Step 1: Don't use VALUE as object name, it's a reserved word.
Step 2: Use table alias names.
Step 3: Use the UNION clause.
Step 4: "Remove" duplicates (you haven't specified a criteria for duplicates).

E.g.

WITH Data1 AS
   (
       SELECT           UI.id,
                        UI.Email,
                        UI.fname,
                        UI.lname,
                        UI.status,
                        UI.parentid,
                        UI.usertype
       FROM             dbo.Userinfo UI
           LEFT JOIN    dbo.groupInfo GI ON UI.ClubId = GI.clubid
                                            AND UI.id = GI.userinfo_id
           LEFT JOIN    dbo.TeamMembers TM ON UI.id = TM.userinfo_id
           LEFT JOIN    dbo.UserInfo_Static_Roles UISR ON UI.id = UISR.userinfo_Id
       WHERE            UI.status <> 3
                        AND (   TM.status IS NULL
                                OR TM.status <> 3 )
                        AND ( UI.ClubId = @clubid )
                        AND (   @roles IS NULL
                                OR UISR.Static_Roles_id IN (   SELECT   [Value]
                                                               FROM     dbo.SPLIT(@roles) ))
                        AND (   @status IS NULL
                                OR UI.STATUS = @status )
                        AND (   @teamid IS NULL
                                OR TM.Teamid = @teamid )
                        AND (   @Groupid IS NULL
                                OR GI.groupid = @Groupid )
                        AND (   @name IS NULL
                                OR UI.lname LIKE @name
                                OR UI.fname LIKE @name
                                OR UI.Email LIKE @name )
   ),
     Data2 AS
   (
       SELECT           UI.id,
                        UI.Email,
                        UI.fname,
                        UI.lname,
                        UI.status,
                        UI.parentid,
                        UI.usertype
       FROM             dbo.Userinfo UI
           INNER JOIN   dbo.LnkParent_ChildIDs ON UI.id = dbo.LnkParent_ChildIDs.ParentUserinfoId
           INNER JOIN   dbo.TeamMembers TM ON dbo.LnkParent_ChildIDs.ChildUserinfoId = TM.userinfo_id
           LEFT JOIN    dbo.groupInfo GI ON UI.ClubId = GI.clubid
                                            AND UI.id = GI.userinfo_id
           LEFT JOIN    dbo.UserInfo_Static_Roles UISR ON UI.id = UISR.userinfo_Id
       WHERE            UI.status <> 3
                        AND (   TM.status IS NULL
                                OR TM.status <> 3 )
                        AND ( UI.ClubId = @CLUBID )
                        AND (   @roles IS NULL
                                OR UISR.Static_Roles_id IN (   SELECT   [Value]
                                                               FROM     dbo.SPLIT(@roles) ))
                        AND (   @status IS NULL
                                OR UI.STATUS = @status )
                        AND (   @teamid IS NULL
                                OR TM.Teamid = @teamid )
                        AND (   @Groupid IS NULL
                                OR GI.groupid = @Groupid )
                        AND (   @name IS NULL
                                OR UI.lname LIKE @name
                                OR UI.fname LIKE @name
                                OR UI.Email LIKE @name )
   ),
     Unioned AS
   (
       SELECT   D.id,
                D.Email,
                D.fname,
                D.lname,
                D.status,
                D.parentid,
                D.usertype
       FROM     Data1 D
       UNION
       SELECT   D.id,
                D.Email,
                D.fname,
                D.lname,
                D.status,
                D.parentid,
                D.usertype
       FROM     Data2 D
   ),
     Ordered AS
   (
       SELECT   U.id,
                U.Email,
                U.fname,
                U.lname,
                U.status,
                U.parentid,
                U.usertype,
                ROW_NUMBER() OVER ( PARTITION BY U.Email
                                    ORDER BY U.fname,
                                             U.lname ) AS RN
       FROM     Unioned U
   )
SELECT  O.id,
        O.Email,
        O.fname,
        O.lname,
        O.status,
        O.parentid,
        O.usertype
FROM    Ordered O
WHERE   O.RN = 1;

Open in new window

No need for a temporary table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
markejAuthor 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 DeveloperCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.