Home > SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Using Built-in system function – IDENTITY()

Using Built-in system function – IDENTITY()


IDENTITY() function can be used to create a new Identity column for a table, You can only use this function in a SELECT statement with INTO clause.

Syntax:

IDENTITY ( data type, seed, increment ) AS Column Name

for example,

I have the following table with two columns – Department Name and Manager ID:

SELECT      *

FROM        dbo.Departments

Result Set:

deptname                  deptmgrid

————————- ———–

HR                        2

Marketing                 7

Finance                   8

R&D                       9

Training                  4

Gardening                 NULL

 

(6 row(s) affected)

Now, if I need to add a new identity column to this table, I can do it using IDENTITY function.

An identity column can be added to above data using:

SELECT      IDENTITY(INT, 100, 2) AS deptid,

            deptname,

            deptmgrid

INTO        dbo.NewDepartments

FROM        dbo.Departments

Result Set:

(6 row(s) affected)

A new table dbo.NewDepartments is now created with an identity column – deptid.

SELECT      *

FROM        dbo.NewDepartments

Result Set:

deptid      deptname                  deptmgrid

———– ————————- ———–

100         HR                        2

102         Marketing                 7

104         Finance                   8

106         R&D                       9

108         Training                  4

110         Gardening                 NULL

 

(6 row(s) affected)

Hope This Helps!

Vishal

  1. No comments yet.
  1. No trackbacks yet.