What's wrong with this code? Issue with copying data across tables

insert into Permission (UserID, perm, Login) select UserID, 0, Login from Users

What happens: Copy data across tables and inserts 0 as a default value for 'perm' column. However, I need to run this code every time the software creates its form. So it keeps adding everything it already have.

Workaround: DELETE FROM Permission WHERE perm=0 --before running the insert code. This will make sure not to delete users who have Perm=1 (permission to alter something).

Issue: So I run the delete code above before running the insert. But since it's a frequent routine it will double the user already contained in the table Permissio开发者_C百科ns (i.e.: user Admin will have perm=0 and perm=1). Is there anyway to detect if the user already exist and skip it or something?

What you could do is

insert into Permission(UserID,perm,Login)
select UserID, 0, Login
from Users
where UserID NOT IN(select UserID from Permission);

Basically what that does is inserts them in if they aren't already there.

This will only insert records that are not already in Permission table by the combination of UserID and Login. Probably, UserID alone is enough if Login is stored as redundant (not-normalized data).

insert into Permission (UserID, perm, Login)
select U.UserID, 0, U.Login
from Users U
left join Permission P on P.UserID = U.UserID and P.Login = U.Login
where P.Login is null

The permission matrix is usually expressed in one of two ways:

1. bit mask - but this gets into scalability issues real quick, so your Perm field contains a mask of all "allowed" bits
2. UserId - PermissionId - Access (bit). This is what I assume your table has (the tuple UserId/PermissionId) and hence the LEFT JOIN form on two fields.

Sure, this will insert only records that do not have a permission record already.

INSERT INTO Permission (UserID, perm, Login) 
SELECT u.UserID, 0, u.Login 
FROM Users u
    LEFT OUTER JOIN Permission p ON u.UserID = p.UserID
WHERE p.UserID IS NULL