CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Justin
Justin

Reputation: 269

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

Upvotes: 1

Views: 61

Answers (2)

Mansoor
Mansoor

Reputation: 4192

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')

Upvotes: 0

Chetan Sanghani
Chetan Sanghani

Reputation: 2111

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

you can also use this query.

Upvotes: 1

Related Questions