Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    3

    Unanswered: 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.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Mar 2003
    Posts
    3
    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.

  4. #4
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    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

    Last edited by TimS; 03-26-03 at 23:13.

  5. #5
    Join Date
    Mar 2003
    Posts
    3
    Tim, that works a treat. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •