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?

Easterling

On a side note never use SELECT * when using with INSERT INTO statement. Always explicitly use the column names in the select and Insert into statement. you never know what order of columns in a source table is same as in the target table. – Shoreless

@Shoreless I know this (SQL 101), I was grabbing the select * from an example I got from another web page. – Easterling

Note this also applies to SQL 2005. It works in later version. – Monolith

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

Avocet

Thanks. I'll update and mark as answered if it solves my problem. – Easterling

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'

Baring

I tested in MS SQL Server 2K. You cannot do a select statement inside a insert values in MS SQL Server 2K. You will get: Subqueries are not allowed in this context. Only scalar expressions are allowed – Easterling

@Easterling Incorrect. This must be a problem with Coldfusion or your access provider (ADO, ODBC, etc.). I assure you that SQL Server 2000 has no problem with it. – Baring

See this: forums.asp.net/t/… – Easterling

BTW: Tested outside of Coldfusion in MS SQL Server 2008 R2 and it works. Tested outside of ColdFusion in MS SQL Server 2000 and I get "Subqueries are not allowed in this context. Only scalar expressions are allowed " – Easterling

@Easterling That link in no way confirms your claim. What they are saying there (and the problem with your original post) is that variable subqueries are not allowed in a VALUES (..) clause. Technically the INSERT..SELECT.. syntax isn't necessarily even a sub-query. – Baring

This is even clearer: decoding.wordpress.com/2008/10/11/… – Easterling

@Easterling I just checked on one of my companies SS2000 databases, and my query worked fine. – Baring

@Easterling Are you even reading these links you are posting?!? They all say exactly what I am saying, INSERT..SELECT.. is the way to do this in SQL Seerver 2000! – Baring

@Easterling - I think you guys got your wires crossed. Sounds like you are still talking about the original query/error, while RBarryYoung is talking about using a different approach: INSERT/SELECT. The query above definitely works, so if it is not working for you, then you are doing something different than what was posted. – Afterburning

@Afterburning Agreed. Definitely seems like more of a mis-communication / misunderstanding than anything. – Baring

After going through the comments from @Easterling and RBarryYoung I come to the conclusion that ConfusedDeer was actually trying to comment on the 1st paragraph of RBerryYoung's reply (not the actual SQL query) where he says "AFAIK, it's perfectly valid. My suspicion is that it's Coldfusion or maybe your access provider that is blocking it.". The reason being that it is NOT perfectly valid in SQL Server 2000 but only in later versions. – Prosimian