Insert data based on a condition

Question

I'm trying to figure out if it's possible to insert a list of objects into a table, using Dapper, but also applying some kind of check to see which data should be included.

var data = new List<TestInsertDataDto>
{
    new TestInsertDataDto { InsertId = 1, Value = 2 },
    new TestInsertDataDto { InsertId = 2, Value = 3 },
    new TestInsertDataDto { InsertId = 3, Value = 6 }
};

await connection.ExecuteAsync(@"
    INSERT INTO test_data (insertid, value)
    VALUES (@InsertId, @Value)
    -- I'd like to do a row per row check here to determine if the row should be
    -- inserted or if this row for some reason shouldn't be inserted. This check
    -- requires a query on the test_data table and a join on another table.", data)
;

Answer 1

You can insert your record conditionally based on another query with EXISTS or NOT EXISTS.
This would look like:

INSERT INTO test_data (insertid, value)
SELECT @InsertId, @Value
WHERE EXISTS (
    SELECT *
    FROM test_data
    WHERE -- some condition
)