locked
Explicit value must be specified for identity column in table MyTable either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. RRS feed

  • Question

  • Please can you help me how to insert ID value  which are having autoincriment . The following error is coming

    Explicit value must be specified for identity column in table  MyTable either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

    SET IDENTITY_INSERT [dbo].[MyTable] ON

    INSERT INTO [dbo].[MyTable]
               ( ID
       ,EmpCode
               ,DateTime
               ,Direction
               ,DeviceId
               ,DownloadDate
               ,LogDate
               ,DeviceLogId)
         VALUES
               ( 21
       ,12 
               ,'2019-03-15 08:50:45.000'
               ,null
               ,null
               ,null
               ,null
               ,null)
    SET IDENTITY_INSERT [dbo].[EsslLogs] Off
    GO


    polachan

    Wednesday, March 20, 2019 10:00 AM

All replies

  • Hi <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="5" id="5">polachan</g>

    could be the identity already is set to "ON" for another table? it must be set to one table at a time.

    I also see you run at the end

    SET IDENTITY_INSERT [dbo].[EsslLogs] Off

    while  in the beginning, you run 

    SET IDENTITY_INSERT [dbo].[MyTable] ON

    it should be the same table

    good luck


    Uri K.

    Wednesday, March 20, 2019 1:26 PM
  • Hi polachan,

    Would you like this one ?

     
    ------If you would like to insert the column 'ID' manually
    If Object_ID('MyTable','U') Is Not Null Drop Table [MyTable]
    go
    create table [dbo].[MyTable]
    ( ID int identity(1,1),
    EmpCode int ,
    DateTime datetime ,
    Direction varchar(20),
    DeviceId int ,
    DownloadDate datetime,
    LogDate datetime,
    DeviceLogId int )
    SET IDENTITY_INSERT [dbo].[MyTable] ON
    INSERT INTO [dbo].[MyTable]( ID,EmpCode,DateTime,Direction,DeviceId,DownloadDate,LogDate,DeviceLogId) VALUES
    ( 21,12 ,'2019-03-15 08:50:45.000',null,null,null,null,null)
    SET IDENTITY_INSERT [dbo].[MyTable] Off
    
    select * from [MyTable]
    /*
    ID          EmpCode     DateTime                Direction            DeviceId    DownloadDate            LogDate                 DeviceLogId
    ----------- ----------- ----------------------- -------------------- ----------- ----------------------- ----------------------- -----------
    21          12          2019-03-15 08:50:45.000 NULL                 NULL        NULL                    NULL                    NULL
    */
    
    ------If you would like to insert the column 'ID' automatically
    SET IDENTITY_INSERT [dbo].[MyTable] Off
    INSERT INTO [dbo].[MyTable]( EmpCode,DateTime,Direction,DeviceId,DownloadDate,LogDate,DeviceLogId) VALUES
    (12 ,'2019-03-15 08:50:45.000',null,null,null,null,null)
    select * from [MyTable]
    /*
    ID          EmpCode     DateTime                Direction            DeviceId    DownloadDate            LogDate                 DeviceLogId
    ----------- ----------- ----------------------- -------------------- ----------- ----------------------- ----------------------- -----------
    21          12          2019-03-15 08:50:45.000 NULL                 NULL        NULL                    NULL                    NULL
    22          12          2019-03-15 08:50:45.000 NULL                 NULL        NULL                    NULL                    NULL
    */
    

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 21, 2019 6:42 AM