Search code examples
sqlsql-serversql-server-2014

SQL insert if doesn't exist with another column

I have the below script which creates a record in USEROUT if the User exists in MOMUSER but does not exist in USEROUT. But, I need check one more column in USEROUT called FILENAME to make sure we create the record if the user doesn't already have a recorder in USEROUT for the FILENAME FORM.FRX... have tried many things -- any ideas?

 INSERT INTO USEROUT (CUSER, FILENAME)
 SELECT MOMUSER.CODE, 'FORM.FRX'
 FROM MOMUSER
 LEFT JOIN USEROUT ON MOMUSER.CODE = USEROUT.CUSER 
 WHERE USEROUT.CUSER IS NULL
Solution

  • Use Not Exists and get your desired result :

      INSERT INTO USEROUT (CUSER, FILENAME)
  SELECT MOMUSER.CODE, 'FORM.FRX'
  FROM MOMUSER
  WHERE NOT EXISTS
  ( SELECT 1 FROM USEROUT WHERE CUSER = MOMUSER.CODE AND FILENAME = 'FORM.FRX')