How do I insert into a unique key into a table?
- by Ben McCormack
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?
