Subqueries are not allowed in this context. Only scalar expressions are allowed

Question

My customer is running MS SQL Server 2000. I completed a project, but I failed to realize that MS SQL Server 2000 will not allow a select inside insert into values thus giving the error:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

When I run the following query:

insert into table_one (greeting_column, name_column)
values (
         'hello',
         (select column_1 from table_to where name = 'bob')
       )

I'm calling this query from coldfusion10. I've already realized a solution utilizing coldFusion10 for this problem by replacing the select statement calling the query and storing the results in a coldFusion list variable, then iterate through a loop that inserts the contents of the CF list variable to its respective record, but this takes much more processing then a simple SQL statement. I've found solution on another web page that got around the issue by doing this (Yes, I know it's bad practice to do a 'select *' this is just an example):

CREATE PROC whatever
@REC int,
@ChangedIP varchar(15),
@ChangedBY varchar(30)
AS
INSERT INTO table_LOG
SELECT *, GETDATE(), @ChangedID, @ChangedBy FROM table WHERE record = @REC

But I don't think coldFusion will allow Transact-SQL Variables in a query (will try after the weekend) Is there a way to re-write not using Transact-SQL Variables?

Answer 1

I don't have a Microsoft SQL Server 2000 anymore, but this should also work, simply replace VALUES with SELECT and remove the brackets:

insert into table_one (greeting_column, name_column)
SELECT 
         'hello',
         (select column_1 from table_to where name = 'bob')

Answer 2

I no longer have SQL Server 2000 to test against, but I have no idea why it would not allow your query, AFAIK, it's perfectly valid. My suspicion is that it's Coldfusion or maybe your access provider that is blocking it.

In any event, I know for sure that the following is valid:

insert into table_one (greeting_column, name_column)
Select 'hello',
        column_1 
from table_to 
where name = 'bob'