@@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?
rignhomCheck @.@.Identity from BOOKS ONLINE to get last-inserted identity value.|||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.|||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|||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|||if Column3 is a does not allow Null, would this trigger be useful?|||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.|||hmm.. that's a way.. but would the cost be too high, since there's two write. And it may hit concurrency as well..|||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|||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?|||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.|||nice ...sql