Should inserting rows with TVP be in transaction?

Question

I have a TVP type List_Of_Items, and want to insert multiple rows at once, so my choice is to use stored procedure with TVP parameter like this below. Is such a inserting safe and I can get rid of the transaction? Or its potenially dangerous as the insert can break in the middle so transaction is must be?

CREATE PROC Insert_Order_With_Details 
(
     @Items List_Of_Items
) 
AS
BEGIN
    BEGIN TRANSACTION
        INSERT INTO OrderDetails (OrderId, CustomerId, ItemId, Quantity)
            SELECT @OrderID, @CustomerID, ItemID, Quantity
            FROM @Items
    COMMIT
END

score 1 · Accepted Answer · answered Jun 30 '19 at 15:55

No, you should only use explicit transactions when you have multiple SQL statements that you want to run as an atomic operation.

Single SQL statements run in an implicit transaction, so any single statement can either complete successfully or fail entirely - you will never have an insert...select that fails in the middle of the select operation and inserts only a part of the rows.

Should inserting rows with TVP be in transaction?

1 Answers1