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.
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 like this:
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 this in a query (will try after the weekend) Is there a way to re-write not using Transact-SQL Variables?