SQL Server - transactions roll back on error?

You can put set xact_abort on before your transaction to make sure sql rolls back automatically in case of error.

You are correct in that the entire transaction will be rolled back. You should issue the command to roll it back.

You can wrap this in a TRY CATCH block as follows

BEGIN TRY    BEGIN TRANSACTION        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);    COMMIT TRAN -- Transaction Success!END TRYBEGIN CATCH    IF @@TRANCOUNT > 0        ROLLBACK TRAN --RollBack in case of Error    -- <EDIT>: From SQL2008 on, you must raise error messages as follows:    DECLARE @ErrorMessage NVARCHAR(4000);      DECLARE @ErrorSeverity INT;      DECLARE @ErrorState INT;      SELECT          @ErrorMessage = ERROR_MESSAGE(),         @ErrorSeverity = ERROR_SEVERITY(),         @ErrorState = ERROR_STATE();      RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);      -- </EDIT>END CATCH

Here the code with getting the error message working with MSSQL Server 2016:

BEGIN TRY    BEGIN TRANSACTION         -- Do your stuff that might fail here    COMMITEND TRYBEGIN CATCH    IF @@TRANCOUNT > 0        ROLLBACK TRAN        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()        DECLARE @ErrorSeverity INT = ERROR_SEVERITY()        DECLARE @ErrorState INT = ERROR_STATE()    -- Use RAISERROR inside the CATCH block to return error      -- information about the original error that caused      -- execution to jump to the CATCH block.      RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);END CATCH