Post reply

Increment Field Dynamically on Insert

  • January 27, 2003 at 2:52 pm

    #81491

    I have a client who, for whatever reason, wants the following.

    An insert from a table (tblSource) with no primary key into a table (tblDest) with a primary key.

    The primary key in tblDest is CustID char(12)

    The primary key is char(12), but contains all numerics. Therefore, the client wants all the new data to be inserted with keys starting at 300000000 and incrementing up.

    What SQL statement can I create, that can be used in a Stored proc that will accomplish this?

    Within my stored proc I have tried Adding a Identity field, inserting records with CustID = 300000000 + IDentityField then dropping the identity field

    This works fine if I execute those 3 statements in succession, but I can't use this approach in a Stored Proc because the second statement fails - it can't find IdentifyField because it doesn't exist until the previous statement . . .

    Any ideas would be greatly appreciated.

    Thanks,

    Matthew



    Matthew Mamet

  • January 27, 2003 at 3:09 pm

    #447353

    I would not recommend creating a column and dropping it dynamically. There is no elegant solution to this request. You could maintain the last used number in something like tbl_system_parameter table, read it and increment every time you run this SP. Or you could scan the table for the maximum used number converted from char(12) to int.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply