NOT EXISTS Query 1

[Jesi523]

I have a stored procedure where I need to insert from some other tables when those items do not exist in the original table. Here’s the stored procedure that I wrote.

ALTER PROCEDURE [dbo].[sp_CreateRequestedItems]

AS
BEGIN
INSERT INTO dbo.RequestItems
([request], [applicationProfile])

--select from requests and profiles table into requestitems table based on dept and job codes
SELECT Requests.rAuid,
Profiles.applicationProfile
FROM Requests
LEFT JOIN Profiles
ON Requests.departmentCode = Profiles.deptCode AND Requests.jobCode = Profiles.jobCode
WHERE NOT EXISTS (SELECT * FROM RequestItems WHERE request <> rAuid)
END

Here’s the error I am getting:
Cannot insert the value NULL into column 'applicationProfile', table 'UserAccess.dbo.RequestItems'; column does not allow nulls. INSERT fails.

The 2 columns I am trying to insert are foreign keys and they cannot be null true, but I don’t know what I am doing wrong. Please help.

 

[markros]

You need to change <> to = in the NOT EXISTS subquery. Also, it's a good idea to add table aliases, e.g.
WHERE RequestItems.Request = Requests.rAuID

PluralSight Learning Library

 

[Jesi523]

I tried to change it to an = sign but I got the same error.

 

[markros]

Why you're using LEFT JOIN with Profiles if you want to insert existing profiles only? Change LEFT JOIN to Inner JOIN and also add condition
AND Profiles.applicationProfile IS NOT NULL

PluralSight Learning Library

 

[Jesi523]

Thank you so much markros, it worked.