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
netadmin2004Asked:
Who is Participating?
 
appariConnect With a Mentor Commented:
do you want to insert all users not existing in UserSecFunction if LookupAccountManager entry is not existing?
if it is so try this

try

INSERT INTO UserSecFunctions
(USERID,FUNCTIONNAME,CREATEDATE,CREATEUSER,MODIFYDATE,MODIFYUSER)
Select
U.UserID,'LookupAccountManager',GetDate(),'ADMIN',GetDate(),'ADMIN'
From
UserInfo U
WHERE
not exists (Select 1 from UserSecFunctions UF
Where UF.FunctionName = 'LookupAccountManager' AND UF.UserID = U.UserID )
0
 
appariCommented:
try

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

0
 
netadmin2004Author Commented:
Awesome thank you!
0
All Courses

From novice to tech pro — start learning today.