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')