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.

Here's a real-world schema:

CREATE TABLE [dbo].[Setting]
  (
     [ID]       [BIGINT] NOT NULL,
     [Module]   [NVARCHAR](400) NOT NULL,
     [Property] [NVARCHAR](400) NOT NULL,
     [Value]    [NVARCHAR](4000) NULL,
     PRIMARY KEY CLUSTERED ( [ID] ASC )
  )

And here's the INSERT statement I'm trying to execute:

INSERT INTO [Setting]
            ([ID],
             [Module],
             [Property],
             [Value])
VALUES      ((SELECT COALESCE(max(ID), 0) + 1
              FROM   [Setting]),
             N'security.catalogs.integrated.integrated',
             N'enable-integrated-authentication',
             N'true') 

For the life of me I cannot see anything special here, yet on at least two occasions this has failed on SQL Server 2008 R2 Express with the following error message:

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

This INSERT statement operates on a completely empty table, no strange options were allegedly configured for SQL Server, no nothing.

How can this be happening? From my understanding of SQL Server, max cannot possibly return more than one row.

share|improve this question
1  
Your code works fine for me on 2008 and 2012 both with an empty table and subsequent inserts. I presume there is some good reason you aren't using an IDENTITY column here? –  Martin Smith Feb 25 '13 at 21:42

2 Answers 2

Experimentally, your code worked for me on both 10.50.1600 (2008 R2 RTM) Developer and 10.0.4000 (2008 SP2) Express. However, it failed against 9.0.5057 (2005 SP4 + hotfix). So, I dug into the documentation.

The 2005 version says this about expressions in the VALUES clause (emphasis mine):

expression

Is a constant, a variable, or an expression. The expression cannot contain a SELECT or EXECUTE statement.

while the 2008 and higher version looks like this:

expression

Is a constant, a variable, or an expression. The expression cannot contain an EXECUTE statement.


From this evidence, I can only assume that the server version where you're executing these queries is not what you think it is.

share|improve this answer

From what I have been reading you are not allowed to use subqueries in insert statements like this. It may work when Setting is empty because it evaluates to NULL. This is the usual way to write a query like this.

 insert into [Setting] ([ID], [Module], [Property], [Value])
 select coalesce(max(ID), 0) + 1, N'security.catalogs.integrated.integrated', 
    N'enable-integrated-authentication', N'true' 
 from [Setting];
share|improve this answer
    
How can max possibly return more than one row? –  Anton Gogolev Feb 25 '13 at 17:31
    
Your right. I missed that. I've corrected my answer above. –  Kenneth Fisher Feb 25 '13 at 17:42
    
For clarity, VALUES can not be used with a query. You either insert data with a query or you insert explicit values using VALUES. –  Mike Fal Feb 25 '13 at 18:24
    
sub queries are fine there. See the grammar for INSERT. It allows VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ] where sub queries are an expression –  Martin Smith Feb 25 '13 at 21:49
    
Fair enough .. I was basing this on the half dozen or so pages I saw when searching. Regardless I think the INSERT INTO SELECT is the better syntax in this case. –  Kenneth Fisher Feb 25 '13 at 21:54

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.