how to use the keyword `VALUES` in an `IN` statement?

Question

I am on sql server 2016

select @@version

Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64) Sep 17 2020 22:12:45 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)

Inside a simple insert statement in sql server I have the key word values

for example:

BEGIN TRANSACTION T1
SELECT @@TRANCOUNT
INSERT INTO [APCore].[usr].[userBusinessArea]
 ([businessAreaID]
 ,[userID])
 VALUES
 (21, 368), 
 (21, 227419) ,
 (21, 99906), 
 (21, 140470)
SELECT [1]= @@TRANCOUNT
--COMMIT TRANSACTION T1
--ROLLBACK
SELECT [2]=@@TRANCOUNT

The problem is that before I run the insert I would like to use the same logic with the values in order to find out if any of these records is already in the table.

otherwise you might end up with this:

enter image description here

the way I have done is:

select top 10 * from
[APCore].[usr].[userBusinessArea]
where businessAreaid = 21
 and Userid in
(368,227419,99906,140470)

this is after the insert:

enter image description here

I saw some examples in here: SQL Server IN Operator

Is there a good and worth way to use values in an in statement?

Answer 1

One option is to reference the VALUES clause just as you would if it were materialised.

INSERT INTO [APCore].[usr].[userBusinessArea]
 ([businessAreaID]
 ,[userID])
SELECT 
 businessAreaID,
 userID
FROM (
 VALUES
 (21, 368), 
 (21, 227419) ,
 (21, 99906), 
 (21, 140470)
) v (businessAreaID, userID)
WHERE v.userID NOT IN (368,227419,99906,140470);

See also the examples in the docs

Try it on db<>fiddle

• Marcello Miorelli: Thank you for the code, the docs reference, and for the fiddle, I thought it was a silly question at first, I am glad I posted as this is going to help me a lot, including in the automating scripts I like to produce.
• SMor: Merge could do this as well without the need for the NOT IN usage.