One doubt related to @@identity

[varshan09]

Suppose I have a table A with two colums - col1 (number) primary key and col2(Varchar(100)). If I insert a row into this table. Can we get value of last inserted value in the column col1 of this table using @@identity. As far as my understantding goes @@identity can be used for identity columns. Is there any way in which we can get value of last inserted column using @@identity?

Thanks
Varsha

 

[miq]

HI,
sure you are right about @@identity but if you wan to get the latest value for col2 as well then you have to use a query something like this;

select col1, col2 from table_name
where col1 = @@identity


Be sure to save @@identity value just after insertion because this value can chage very fast.


B.R,
miq

 

[varshan09]

How do we save @@identity? I tried this query and it is not giving me any record.
INSERT TABLE2
SELECT 'Varsha', Max(col2) + 1
FROM TABLE2

select col1, col2 from TABLE2
where col2 = @@identity

here, col2 is not an identity column.

Let me know if I'm missing anything.

Thanks
Varsha

 

[donutman]

You save it like this:

[Blue]DECLARE[/Blue] @Identity [Blue]int[/Blue]
[Blue]INSERT[/Blue] [Blue]INTO[/Blue] YourTable
   [Blue]SELECT[/Blue] Fields
[Blue]SET[/Blue] @Identity[Gray]=[/Gray][Fuchsia]@@Identity[/Fuchsia][green]
--However THAT IS NOT RECOMMENDED, it is not reliable.
[/green][green]--Better to use this:
[/green][Blue]SET[/Blue] @Identity[Gray]=[/Gray][Fuchsia]Scope_Identity[/Fuchsia][Gray]([/Gray][Gray])[/Gray]

-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[miq]

hi,

select col1, col2 from TABLE2
where col2[/red] = @@identity

you cannot get correct results unless col2 in above query in not an identity column.

save @@identity value in a varibale just after insert or update using @@identity or better scope_identity and change your where clause accordingly.

donutman has has already taught
you on how to save @@identity value


This forum is not about giving you the code snippit only but to teach in general.

B.R,
miq