Built-in Global Variables

SQL have lots of built-in variables that are very useful to use in queries and scripts.

@@IDENTITY

After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.

Example:

Given to tables; SCHOOL and COURSE:

SCHOOL table:

COURSE table:

We want to insert a new School into the SCHOOL table and we want to insert 2 new Courses in the COURSE table that belong to the School we insert. To find the “SchoolId” we can use the @@IDENTITY variable:

declare @SchoolId int

-- Insert Data into SCHOOL table
insert into SCHOOL(SchoolName) values ('MIT')

select @SchoolId = @@IDENTITY


-- Insert Courses for the specific School above in the COURSE table insert into COURSE(SchoolId,CourseName) values (@SchoolId, 'MIT- 101')
insert into COURSE(SchoolId,CourseName) values (@SchoolId, 'MIT- 201')

The result becomes:

SCHOOL table:

COURSE table: