24 May 2011

Insert value into Identity Column

  • Create Table
    CREATE TABLE [dbo].[TblMyFriends]
    (
                [FriendId] [int] IDENTITY(1,1) NOT NULL,
                [FriendName] [varchar](50) NOT NULL
    )
    
  • Insert Values
    Insert into [TblMyFriends](FriendName) values('Dipal')
    Insert into [TblMyFriends](FriendName) values('Sai')
    Insert into [TblMyFriends](FriendName) values('Harshad')
    Insert into [TblMyFriends](FriendName) values('Pankaj')
  • Insert Values with Explicity Identity Value
    Insert into [TblMyFriends](FriendId,FriendName) values(44,'New Friend')
    --Throw Error
    --   Cannot insert explicit value for identity column in table 'Employee' when IDENTITY_INSERT --is set to OFF.
    
    SET IDENTITY_INSERT [TblMyFriends] ON
    INSERT INTO dbo.[TblMyFriends](FriendId,FriendName) VALUES(44, 'New Friend')
    SET IDENTITY_INSERT dbo.[TblMyFriends] OFF
    

Hope This will help you,Expecting Comments....