Subqueries are not allowed in this context. Only scalar expressions are allowed
You could use this code instead - just use the SELECT to provide the values, including a fixed literal:

INSERT INTO dbo.table_one (greeting_column, name_column)
   SELECT
      'hello', column_1 
   FROM 
      dbo.table_to 
   WHERE 
      name = 'bob'


You cannot mix the VALUES() and the SELECT methods of providing values for an INSERT - either you can provide literals and SQL variables for a VALUES(.....) list of values - or then you need to use the SELECT instead