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?
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.

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
Guy Hengel [angelIII / a3]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

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
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
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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.