Subqueries are not allowed after VALUES?

问题:

INSERT INTO t_MT_User (ID, Badge, Name, Scope, comp_code, dept_code, [status]) 
VALUES ((SELECT MAX(ID) + 1 FROM t_MT_User), @userBadgeNumber, @userName, @userScope, @companyCode, @departmentCode, 1)

This query throws the following error:

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

If I change VALUES to SELECT, I get another error instead:

INSERT INTO t_MT_User (ID, Badge, Name, Scope, comp_code, dept_code, [status]) 
    SELECT 
        ((SELECT MAX(ID) + 1 FROM t_MT_User), 
         @userBadgeNumber, @userName, @userScope, @companyCode, 
         @departmentCode, 1)

Incorrect syntax near ','.

How do I achieve (SELECT MAX(ID) + 1 FROM t_MT_User) in this context?

回答1:

First thing is first - your code, even if you fix it's syntax, is wrong. Seems like you try to implement your own auto-increment mechanism. That will fail.
The correct way is to use SQL Server's built-in mechanism for auto-increment, and create the ID columns as an Identity.

Then you don't need to include it in the insert statement at all, and you are safe even in a multi-client or multi-threaded environments (which your current implementation will start giving wrong results).

回答2:

your syntax is wrong. Should be

INSERT INTO t_MT_User (ID, Badge, Name, Scope, comp_code, dept_code, [status]) 
SELECT MAX(ID) + 1 , @userBadgeNumber, @userName, @userScope, 
       @companyCode, @departmentCode, 1
FROM   t_MT_User