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 TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception
    RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)
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
    COMMIT
END TRY
BEGIN 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