aiDeeva.com

Tag: Identity

  • Script to determine identity table and identity column

    Script to determine the identity tables and columns in the databases.

    USE <DBNAME>
--script to determine the table with the identity column
select name, OBJECTPROPERTY(id, 'TableHasIdentity') AS TableHasIdentityCol
from sysobjects
where xtype = 'U'

--script to determine the table and columne with the identity on
SELECT OBJECT_NAME(id) as TblName, name as ColName
FROM syscolumns
WHERE status = 0x80

--script to determine the table and columne with the identity on using ColumnProperty
SELECT OBJECT_NAME(id) as TblName, name as ColName
FROM syscolumns
WHERE COLUMNPROPERTY(id, name, 'IsIdentity') = 1

  • How to insert value into IDENTITY column?

    If you will try to insert the value into Identity column you will get the one of the below error.

    Error 1:
    Msg 544, Level 16, State 1, Line 1
    Cannot insert explicit value for identity column in table ‘Employee’ when IDENTITY_INSERT is set to OFF.

    Error 2:
    Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

    Solution:
    Write SET IDENTITY_INSERT table name ON before the insert script and SET IDENTITY_INSERT table name Off after insert script.

    Example,

    use db1

create table Employee
(
	myID int identity(100,1),
	name varchar(20)
)

insert into Employee(name) values('Jugal')

--if i will try to insert the value into Identity column it will fail
insert into Employee(myID,name) values (101,'DJ')

--you can add the data into identiy column by turning on the IDENTITY_INSERT ON

SET IDENTITY_INSERT Employee ON
	insert into Employee(myID,name) values (101,'DJ')
SET IDENTITY_INSERT Employee OFF