SQL Insert statement using Join

Hi all,

Let's say you have 2 tables (UserSecFunctions and UserInfo). The UserInfo table holds all UserIDs you need to insert into the UserSecFunctions along with some other minute data. How would this statement be written?

I would assume something like the folowing, but I know it's wrong:

--Insert Security Records
INSERT INTO UserSecFunctions UF Join UserInfo U on U.UserID = UF.UserID
(USERID,FUNCTIONNAME,CREATEDATE,CREATEUSER,MODIFYDATE,MODIFYUSER)
VALUES
(U.UserID,'LookupAccountManager',GetDate(),'ADMIN',GetDate(),'ADMIN')
WHERE (UF.FunctionName <> 'LookupAccountManager') AND (UF.UserID = '' or UF.UserID is null)

I hope by backwards logic makes sence. I really only need a way of getting the value 'LookupAccountManager' into UserSecFuctions for all the users in UserUnfo... Sounds real simple though as usual I like to complicate things...

Thanks in advance,

-RJ