Retrieving Autoincrement value after insert

[MattSmithProg]

Hi,

I am inserting a row of data in to a table and as the primary key determing the maximum field for that value and adding one. I would like to know how I can return this as soon as I have done it. I would normally just use a simple select query to return the max record but as there are multiple users this may not return the correct value. My insert query is this:

INSERT INTO PTAPP_TTN_Main SELECT (COALESCE(MAX(Item_No), 0)) + 1, '16/04/2002', Null, 'set gtg stgtg ', 2, -1, '', 7, 5, 'a f gagaeeqa tet qety qty y', 'UBD2O', '' FROM PTAPP_TTN_Main


Where Item_No is the Primary Key and the number I am wanting to return.

Any ideas would be greatly appreciated. Matt Smith

No two nulls are the same

 

[bperry]

I feel sure you've considered this, but I'm wondering, if this was a stored procedure, would something like this work:

declare @nextItem int
select @nextItem =
(COALESCE(Max(IdNum),0)) + 1 from PTAPP_TTN_Main

INSERT INTO PTAPP_TTN_Main SELECT @nextItem, '16/04/2002', Null, 'set gtg stgtg ', 2, -1, '', 7, 5, 'a f gagaeeqa tet qety qty y', 'UBD2O', '' FROM PTAPP_TTN_Main

 

[MattSmithProg]

No I hadn't thought of a stored procedure but it would probably work. I have never tried a SP before but I will give it a go.

I'll let you know how I go.

Thanks once again for your help bperry. I would give you a star but my browser won't support JScript. Matt Smith

No two nulls are the same

 

[bperry]

Okay.
Stored procedures are a terrific and versatile utility, and they will take your SQL Server programming ability to a whole new level.

BTW, I hope this is not a large table, or else the number of Inserts are relatively few: if so, doing that MAX() function repetitively could give bad performance.

Upgrade your browser!

 

[MattSmithProg]

Thanks for your comments bperry. The table will only grow to a maximum of 1000 inserts in any one year. So Max should not be a problem.

I use IE 5.5 but due to constraints at work very few people are allowed to have external internet access to their dektop and as such any scripting language has been disabled for external sites. Sorry Matt Smith

No two nulls are the same