MySQL Syntax error message "Operand should contain 1 column(s)"

Try removing the parenthesis from the SELECT clause. From Microsoft TechNet, the correct syntax for an INSERT statement using a SELECT clause is the following.

INSERT INTO MyTable  (PriKey, Description)
       SELECT ForeignKey, Description
       FROM SomeView

The error you're getting, "The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay.", is actually correct, assuming you have many rows in both BOOK and temp_cheques. You are trying to query all rows from both tables and make a cross-reference, resulting in an m*n size query. SQL Server is trying to warn you of this, before performing a potentially long operation.

Set SQL_BIG_SELECTS = 1 before running this statement, and try again. It should work, but note that this operation may take a long time.

Does B contain the UNITS column?

What is the table structure for temp_cheques and Book?

EDIT: As I said in comments, all the columns should be numeric when doing +/- and when comparing.
Does the following simple SELECT work?

SELECT b.START_NUMBER+b.UNITS-1 FROM Books B

I don't have a MySQL instance handy, but my first guess is the WHERE clause:

WHERE a.number BETWEEN b.START_NUMBER AND b.START_NUMBER+b.UNITS-1;

I imagine that the MySQL parser may be interpreting that as:

WHERE number
(BETWEEN start_number AND start_number) + units - 1

Try wrapping everything in parentheses, ie:

WHERE a.number BETWEEN b.START_NUMBER AND (b.START_NUMBER + b.UNITS - 1);