Post reply

INSERT Identity

  • August 22, 2007 at 5:20 am

    #103404

    I am using an INSERT statement and need to set one of the other fields = to the identity field.
     
    If I use @@Identity in the insert statment - it gives me the ID of the previuos insert the last time the statement was run.
     
    Similarly scope_identity etc
     
    I am using an insert statement which can insert many rows.
     
    I could suppose follow up my insert statement with a satemnet that then sets the other field = to the identity - but is there a better way??

    THanks

  • August 22, 2007 at 5:40 am

    #728493

    yes, you can set your column as a computed column

     

    Everything you can imagine is real.

  • August 22, 2007 at 5:47 am

    #728494

    suppose your identity column is A, and you want to set B = A then

    Alter Table TableName ADD

    B as A

    Everything you can imagine is real.

  • August 22, 2007 at 5:52 am

    #728496

    Hi - Thanks - but I don't want the value always to be the same as the identity - only in certain circumstances.

  • August 22, 2007 at 6:02 am

    #728499

    give us all the necessary information so that we can be of better help to you

    Everything you can imagine is real.

  • August 22, 2007 at 6:07 am

    #728501

    That is all the information.

     

    I want to set a field value equal to the identity field on insert of records. (the field may in other circumstances have a valiue other than the identity.)

    Thanks

  • August 22, 2007 at 6:13 am

    #728502

    This works fine for me.

    create table t(

      id int identity(1,1),

      copy_of_id int)

    insert into t values(isnull(scope_identity(),0)+1)

  • August 22, 2007 at 6:16 am

    #728504

    in what circumstances

    (the field may in other circumstances have a valiue other than the identity.)

    you can use an expression on the column

    Everything you can imagine is real.

  • August 22, 2007 at 6:27 am

    #728515

    For single row insert

    INSERT INTO

    (othercolumn) VALUES (0)

    UPDATE

    SET [othercolumn] = [identitycolumn] WHERE [identitycolumn] = SCOPE_IDENTITY()

    wrap in transaction if preferred

    this will not handle multiple inserts ie INSERT ... SELECT ... FROM ...

    in that case use a trigger

    Far away is close at hand in the images of elsewhere.
    Anon.

  • August 22, 2007 at 6:59 am

    #728539

    When the user specifies another value - I suppose I could set a default on the table field in that case?

  • August 22, 2007 at 7:03 am

    #728540

    in that case I would propose you use a trigger on insert or update on the table, checking if the inserted value is null

    Everything you can imagine is real.

  • August 22, 2007 at 8:14 am

    #728579

    As a general rule, when 'cascading' inserts, you need to use one or more triggers in order to preserve the correct associations across tables.

    I am concerned when you specify that the user can indicate other content than the foreign key is to be used. Is this a case where there is an update, instead of an insert of a new record, but records are being inserted into other tables and need the fk of the updated record instead of the inserted one? If so, then you can use a trigger and ignore the consideration of insert or update, because the information after update referenced the same way as a new insertion. I forget the kb references - you can search on delete, insert and update in the forums to find it - within the last 3 months.

  • August 22, 2007 at 9:24 am

    #728604

    is it possible for you to tell us what this column does?

    Everything you can imagine is real.

  • August 22, 2007 at 9:52 am

    #728623

    The column links to a record within the same table - in some cases itself in other cases a differnat record.

     

    Ps I haven't had chance to try solutions as my network is playing up.

Viewing 14 posts - 1 through 13 (of 13 total)

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