Post reply

Finding the current table name in the context of an insert statement

  • March 19, 2008 at 2:40 pm

    #791721

    you would have been able to keep the existing identity values by using

    set identity_insert yourtable on

    perform your inserts

    set identity_insert yourtable off

    This way you would have been able to load the existing data as is.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • March 20, 2008 at 4:01 pm

    #792294

    tuseau (3/19/2008)

    The problem itself wasn't quite what I was making of it. I didn't need to return the table name from the context of the insert statement after all.

    Basically I was needing to create a very large update script which was independent of the identity column. So, if the database had been purged and then the script was re-run, then the ID column would not be the same (since SQL Server keeps a record of the running value each identity column). The existing script was written by someone else and it involved referencing hard-coded ID values - and the database has a lot of foreign keys, so for each insert/update statement, it referenced an identity explicitly.

    I was able to get round the main issue by using the ident_current('TABLENAME') system function for populating most foreign keys (just subtracting the number of "steps back" need to go to find the row I am referncing). I then wrote some user-defined functions to look up the identity value for other tables which I knew had a unique identifier/constant value.

    Thanks again to all who helped.

    Thank you...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 20, 2008 at 7:26 pm

    #792313

    ALZDBA (3/19/2008)

    you would have been able to keep the existing identity values by using

    set identity_insert yourtable on

    perform your inserts

    set identity_insert yourtable off

    This way you would have been able to load the existing data as is.

    Yeah, I knew about that too, but for various reasons I decided on the "counting back" method.

Viewing 3 posts - 16 through 17 (of 17 total)

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