If Not Exists Insert inside a loop which record updated

Question

Using SQL Server 2016, I have a very simple IF NOT Exists ..... INSERT statement inside a loop. This works well, as expected. Is there a way to identify which records existed therefore did not insert and which records did not exist therefore were inserted without using a second query?

I can easily achieve this by executing a SELECT, then if not found I insert however this is two queries in my loop. I would like to try and achieve this in one query if possible?

The purpose of this is to show the user:

Chris is now enrolled in English
Chris is now enrolled in Maths
Chris was already enrolled in Science

So 1 and 2 did not exist therefore inserted. 3 did exist therefore did not insert

and example of this loop for jon:

    for i = 0 to numOfCourses
        sql = IF NOT EXISTS (SELECT fieldName from tableName 
        WHERE courseID = 1 AND directoryID = 2)
        BEGIN
        INSERT INTO tableName(courseID, directoryID)
        VALUES (1, 2) 
        END 
    next

Please post the code you already built. Please provide sample data and desired results. I use this tool to generate [ASCII tables](https://ozh.github.io/ascii-tables/). — Jon Jaussi, Dec 24 '18 at 01:54
@JonJaussi I have already posted my desired result. The code I have is simply a if not exists then insert query in a loop. I have now included an example of this — Mat41, Dec 24 '18 at 02:06
Maybe this helps: https://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert, use the `OUTPUT` clause. — hlg, Dec 24 '18 at 03:03
I don't see what your desired output has to do with the query you have provided. How do you distinguish between "is enrolled" and "was enrolled"? — Gordon Linoff, Dec 24 '18 at 03:42
To expand on hlg's suggestion: A set-based solution would use a _numbers table_ to supply the values so that only a single `insert` would be needed. An `output` clause can be used to capture which rows were inserted. The result can be joined with the numbers table to identify the rows that were not inserted, i.e. the rows that were already present. Aside: Curious thing that you don't use `i` inside the loop, you just keep trying to process the same row over and over. — HABO, Dec 24 '18 at 03:57
HI all thanks for all the suggestions. The solution used the OUTPUT clause. Thank you to the three users who pointed me in this direction. Off for xmas for me now. Thanks again! — Mat41, Dec 24 '18 at 04:32

Jon Jaussi · Answer 1 · 2018-12-24T03:15:50.420

0

@@ROWCOUNT might prove useful here...

INSERT INTO tableName(courseID, directoryID)
SELECT 1 as courseID, 2 as directoryID 
WHERE NOT EXISTS (SELECT 1 FROM tableName WHERE courseID = 1 AND directoryID = 2);

SELECT CASE WHEN @@ROWCOUNT = 1 
            THEN 'Chris is now enrolled in English'
            ELSE 'Chris was already enrolled in English' END as my_result;

Using this approach, you do not need the IF statement in the loop or the initial check of the existing records.

I adapted this answer from: How to get number of rows inserted by a transaction

Hope it helps.

edited Dec 24 '18 at 03:15

answered Dec 24 '18 at 02:21

Jon Jaussi

1,298
3
18
36

Hi Jon I think you have miss understood my question. Using a IF NOT EXISTS INSERT Is there a way to identify which records existed therefore did not insert and which records did not exist therefore were inserted without using a second query? – Mat41 Dec 24 '18 at 02:36
@Mat41 let me know if this is closer to what you are looking for – Jon Jaussi Dec 24 '18 at 03:02

score 0 · Answer 2 · answered Dec 24 '18 at 04:12

You can use MERGE (Transact-SQL) command with combination of OUTPUT Clause (Transact-SQL)

Merge will perform conditional insert and OUTPUT will return/save inserted records.
Then you simply compare given records with inserted and those which not exists in the inserted collection already existed in the table.

DECLARE @InsertedNames AS TABLE (Name VARCHAR(200))
DECLARE @GivenNames AS TABLE (Name VARCHAR(200))
INSERT INTO @GivenNames VALUES ('One'), ('Two')

MERGE INTO TableName AS t  
USING (SELECT Name FROM @GivenNames) AS given (Name)  
    ON t.Name = given.Name  
WHEN NOT MATCHED THEN  
    INSERT (Name) VALUES (given.Name)  
OUTPUT inserted.Name INTO @InsertedNames;

-- return all names with result.
SELECT 'inserted', Name FROM @InsertedNames  
UNION ALL  
SELECT 'already existed', g.Name 
FROM @GivenNames g 
WHERE g.Name NOT IN (SELECT Name FROM @InsertedNames)

If Not Exists Insert inside a loop which record updated

2 Answers2