Getting Last inserted Identity value in SQL server

In this article I would like to share my idea about getting Identity after a row was inserted in to the SQL Server 2005.
After inserting a row into the database which has primary key feild, most of the time we need the identity, We have three approches based on our reqiurements and situations.

SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT(‘TableName’)

All of the abouve three will get the identity value but in different approches.

The variable @@IDENTITY will return the last generated identity value produced on a connection, without based on the table that produced the value. While @@IDENTITY is limited to the current session, it is not limited to the current scope. This means that if we insert some record in Table1 which has a trigger on the insert and the trigger inserts a record in some other table2 then the @@IDENTITY will return the identity value inserted in Table2.

SCOPE_IDENTITY() will return the last IDENTITY value produced on a connection and by a statement in the same scope, without based on the table that produced the value. So we can say that this function is some identical to @@IDENTITY with one exception. like @@IDENTITY will return the last identity value created in the current session, but it will also limit it to your current scope as well . So that means it will return identity value inserted in Table1.

Use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
IDENT_CURRENT will reutrn returns the last IDENTITY value produced in a table, Without based on the connection that created the value, and Without based on the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session., So it will retrieve the last generated table identity value.

Copy table

select * into employee1  from employee where id=5

select * into employee1  from employee

//CREATE NEW TABLE AND COPY ALL THE TABLE CONTENT

select * from employee1

drop table employee1

SELECT *

INTO employee1

FROM employee

WHERE 1=2

//ONLY COPY STRUCTURE

INSERT INTO employee1 (empname, address)

SELECT empname, address

FROM employee

WHERE id = 2

select * into <destination table> from <source table>

Example:

Select * into employee_backup from employee

 We can also select only a few columns into the destination table like below

select col1, col2, col3 into <destination table>

from <source table>

Example:

Select empId, empFirstName, empLastName, emgAge into employee_backup

from employee

Use this to copy only the structure of the source table.

select * into <destination table> from <source table> where 1 = 2

Example:

select * into employee_backup from employee where 1=2

Use this to copy a table across two database in the same Sql Server.

select * into <destination database.dbo.destination table>

from <source database.dbo.source table>

Example:

select * into Mydatabase2.dbo.employee_backup

from mydatabase1.dbo.employee

Iterate through the table and calculate the total price through storeprocedure

create proc test
as
Select price_id Into  #Temp From   agreement_price
Declare @Id int
declare @val int
Declare @calc int
declare @it int

set @val=0
set @it=(Select Count(*) From #Temp)
While (@it) > 0
Begin
  set @calc=(select price from price where price_id in (Select Top 1 price_id From  #Temp))
  set @val=@val+@calc
set @id=(Select Top 1 price_id From  #Temp)
 Delete from #Temp Where price_id= @Id
set @it=@it-1
End
select @val
exec test