How do I insert into a unique key into a table?

Question

I want to insert data into a table where I don't know the next unique key that I need. I'm not sure how to format my INSERT query so that the value of the Key field is 1 greater than the maximum value for the key in the table. I know this is a hack, but I'm just running a quick test against a database and need to make sure I always send over a Unique key.

Here's the SQL I have so far:

INSERT INTO [CMS2000].[dbo].[aDataTypesTest]
           ([KeyFld]
           ,[Int1])
     VALUES
           ((SELECT Max([KeyFld]) FROM [dbo].[aDataTypesTest]) + 1
           ,1)

which errors out with:

Msg 1046, Level 15, State 1, Line 5 Subqueries are not allowed in this context. Only scalar expressions are allowed.

I'm not able to modify the underlying database table. What do I need to do to ensure a unique insert in my INSERT SQL code?

Answer 1

INSERT INTO [CMS2000].[dbo].[aDataTypesTest]
           ([KeyFld]
           ,[Int1])
SELECT Max([KeyFld]) + 1, 1 FROM [dbo].[aDataTypesTest]

But why not use an IDENTITY property on KeyFld and then either SCOPE_IDENTITY() or an OUTPUT clause? (OK, no schema changes. Bummer)

Answer 2

You can use the other INSERT syntax:

INSERT INTO [CMS2000].[dbo].[aDataTypesTest] 
       ([KeyFld] 
       ,[Int1]) 
SELECT Max([KeyFld]) + 1, 1 FROM [dbo].[aDataTypesTest]

Sorry you can't modify the database. You're right; this is a hack.