CODEWITHSUNDEEP
sql-serverinsertnotin
user1673665
user1673665

Reputation: 522

Insert into SQL table only for records with no entry

I have two tables:

  1. cSc_user: here is every user stored. PK is camosGUID
  2. cSc_UserRole: when a user has a role, a row for this user is stored here

For every role the user has, there is one entry in cSc_userRole. FK for camosGUID in this table is UserGUID. There is also a column RoleGUID. I want only consider one role which has the RoleGUID = AD3BB.

Now my target is to create a record in cSc_UserRole for every user who doesn't have an entry for the role with the RoleGuid = AD3BB. Additionally I want to exclude two users with the camosGUID = -1 and 2032.

This is what I've done already. The problem is it creates an entry in cSc_UserRole also if the user has already the role. So it's duplicated after running the query.

INSERT INTO [csc_userrole] ([RSRC], [Deleted], [DateNew], [DateChanged],
                            [UserNew], [UserChanged], [camosGUID],
                            [UserGUID], [RoleGUID])
    SELECT
        0, 0, GETDATE(), GETDATE(),
        2032, 2032, NEWID(),
        camosGUID, 'AD3BB'
    FROM 
        [cSc_User] 
    WHERE 
        csc_user.camosguid <> -1 
        AND csc_user.camosguid <> 2032 
        AND (csc_user.camosguid NOT IN (SELECT userguid
                                        FROM cSc_Userrole) OR 
             csc_user.camosguid IN (SELECT userguid
                                    FROM cSc_Userrole
                                    WHERE roleguid <> 'AD3BB' 
                                      AND roleguid <> 'E5DEE'))

Upvotes: 1

Views: 59

Answers (1)

S3S
S3S

Reputation: 25112

The query below would return the users you are looking for. I would leave this in a view.

select * 
from cSc_user
where
   --users that don't have a role of AD3BB
   UserGUID not in (select UserGUID from cSc_user where RoleGUID = 'AD3BB')
   --the camosGUIDs you want to exclude
   and camosGUID  not in (-1,2032)

So, in a view...

create view myUserList
as
select * 
from cSc_user
where
   --users that don't have a role of AD3BB
   UserGUID not in (select UserGUID from cSc_user where RoleGUID = 'AD3BB')
   --the camosGUIDs you want to exclude
   and camosGUID  not in (-1,2032)

Then query from it...

select * from myUserList

Upvotes: 1

Related Questions