Conditional insert/Delete

I have a table of employee groups and user names

GroupName  userID
--------------- ---------
abc               jono
a123             jono
thatf              fredds

I query to get an updated list of groups that all users are in.

now I want to update my emp table to insert any new groups that the user is in and then a second query to delete any groups that they are no longer in.

I was thinking somthing like

insert into employeeGroups
select * from #tmpEmp where  ()

but not sure how to exclude the rows that already exist
             
LVL 9
JonMnyAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
here we go:
-- adding new groups 
INSERT INTO employeeGroups ( GroupName, UserID )
  SELECT t.GroupName, t.UserID
  FROM #tmpEmp t 
  WHERE not exists ( SELECT NULL
          FROM employeeGroups eg
          WHERE eg.Groupname = t.GroupName
            AND eg.UserID = t.UserID 
             ) 
-- remove the ones that are no longer in the list:
DELETE eg
FROM employeeGroups eg
  WHERE not exists ( SELECT NULL
          FROM #tmpEmp t
          WHERE eg.Groupname = t.GroupName
            AND eg.UserID = t.UserID 
             )

Open in new window

0
 
brejkCommented:
I need some additional explanations... Give me the names of your tables and specify from which tables you need to query the data and into which tables you need to put the data (and also define what data you want to copy).
0
 
TextReportCommented:
OK please see the INSERT and DELETE statements both are using NOT EXISTS but the tablenames get switch around.
Cheers, Andrew
INSERT INTO employeegroups
SELECT GroupName, UserID
FROM tblNewList NL
WHERE NOT EXISTS (SELECT 'x'
                  FROM employeegroups
                  WHERE grouldname = NL.groupname
                  AND userid = NL.UserID
                 )
 
DELETE FROM employeegroups EG
WHERE NOT EXISTS (SELECT 'x'
                  tblNewList NL
                  WHERE grouldname = EG.groupname
                  AND userid = EG.UserID
                 )

Open in new window

0
 
JonMnyAuthor Commented:
Thanks again, angellIII.
0
All Courses

From novice to tech pro — start learning today.