Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Th

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?

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.