How do I insert into a unique key into a table?
开发者_运维百科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?
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.
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)
精彩评论