Insertion With Explicit IDENTITY

Hi,
I'm writing a database interface to someone else's code and am getting passed INSERT statements that look something like this:

INSERT INTO my_table(my_id, my_val)
VALUES (%s, 5)

My code is expected to replace the %s with a suitable value for an ID. Now, my_table looks like this:

CREATE TABLE my_table (
my_id INT IDENTITY(1,1) PRIMARY KEY,
my_val INT
)

and I'm trying to get a suitable value for the '%s' such that the insert will work correctly. I understand that I need to 'SET IDENTITY_INSERT my_table ON' prior to the insert, but what can I replace '%s' with? I can't put a separate SELECT statement in there, and @.@.IDENTITY is too global to be useful (it's a multithreaded app with a lot of inserts across multiple tables). Hacking the input string to remove the 'my_id, ' and '%s, ' completely is not allowed (unfortunately).

I've tried NULL in the hope that SQLServer will work it out but it complains. I don't want to do a 'SELECT IDENT_CURRENT('my_table')' prior to the INSERT due to the overhead and potential concurrency problems. Is there some special keyword or similar I can put in here?

DB is SQLServer2000 SP2. Any help is greatly appreciated.

Cheers,
Jim.Sounds like you have an application that was designed to run against diffrent vendor databases OR someone has missed the design boat. I don't understand why you would define a table with an identity attribute AND have a requirment that you pass a value for the identity value.

To my knowledge there is no way to pass a place hold in the fashion you have described.|||Originally posted by Paul Young
Sounds like you have an application that was designed to run against diffrent vendor databases OR someone has missed the design boat. I don't understand why you would define a table with an identity attribute AND have a requirment that you pass a value for the identity value.

The reason is that the app talks to a number of different back-end databases and they all have different ways of doing an insert on a table with a unique ID. For instance, Postgres expects to have nextval('my_table_f_id_seq') or similar in place of the %s. I'm just stuck (really stuck, as it turns out) with coding to the supplied spec.

Thanks for the reply.|||SET IDENTITY_INSERT my_table ON

INSERT INTO my_table(my_id,my_val)
VALUES (IDENT_CURRENT('my_table') + 1, 5)

SET IDENTITY_INSERT my_table OFF

May want to replace the + 1 with call to function IDENT_INCR()

Tim S|||Tim, that works a treat. Thanks!