Results 1 to 12 of 12

Thread: @@identity

  1. #1
    Join Date
    Jun 2003
    Posts
    6

    Exclamation Unanswered: @@identity

    Consider Table A having an Identity column.

    If when I want insert a record to the table, I want to have the autogenerated value to be inserted into another column within the same table and same row, how am I going to do it?

    rignhom

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Check @@Identity from BOOKS ONLINE to get last-inserted identity value.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jul 2003
    Location
    Ontario, Canada
    Posts
    4
    Originally posted by Satya
    Check @@Identity from BOOKS ONLINE to get last-inserted identity value.
    Actually, to be safer, I have found that SCOPE_IDENTITY() is better than @@IDENTITY. Both do the same thing, but if you happen to have 2 identities being created within different execution scopes, SCOPE_IDENTITY always seems to return the correct one.

  4. #4
    Join Date
    Jun 2003
    Posts
    6
    Hi, what I want is
    consider TableA with (Column1 Identity, Column2, Column3).

    When I insert into this table
    I want to issue sort of

    INSERT INTO jobs (Column2,Colum3)
    VALUES ('Col2Value', @@IDENTITY)

    I want Column3 to have the same value as the newly autogenerated value for Column1.

    Thanks
    rignhom

  5. #5
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Code:
    CREATE TRIGGER TRG_NAME ON TableA
    FOR INSERT
    AS
    BEGIN
      DECLARE @ID BIGINT
      SELECT @ID = Column1 FROM INSERTED
      
      UPDATE TableA
      SET Column3 = @ID
      WHERE Column1 = @ID
    END
    Johan

  6. #6
    Join Date
    Jun 2003
    Posts
    6
    if Column3 is a does not allow Null, would this trigger be useful?

  7. #7
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    I don't know of another way of retrieving the id value, because the values for either @@identity and scope_identity() are only know after the insert in the table. To solve your problem you can define a default value of let's say 0 (zero), this value will then be overwritten by the trigger.
    Johan

  8. #8
    Join Date
    Jun 2003
    Posts
    6
    hmm.. that's a way.. but would the cost be too high, since there's two write. And it may hit concurrency as well..

  9. #9
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Try this, use a computed column over a trigger

    create table blah (ikey int identity(1,1), column2 varchar(20),column3 as ikey)

    insert into blah select 'joe'

    select * from blah

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  10. #10
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Try this, use a computed column over a trigger

    create table blah (ikey int identity(1,1), column2 varchar(20),column3 as ikey)

    insert into blah select 'joe'

    select * from blah
    Can you explain what the intention is? Does your insert consists of two selects? How do you know the value for column3 before the actual insert?
    Johan

  11. #11
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    If you run this after running my prior SQL

    select * from syscolumns where name in ('ikey','column2','column3')

    You will see that ikey and column 3 are almost identical (except for xoffset which I am not sure what it's for, BOL says internal use only)

    That makes me think that in one insert it is seeing the insert of those two columns as the same, that's why you are able to specify the insert with only one value even though you have three columns. Not sure if that is answering your question and I'm not sure exactly how it works but I know it's faster and preferred than a Trigger.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  12. #12
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227

    Thumbs up

    nice ...
    Johan

Posting Permissions

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