none
IDENTITY_INSERT ON

    Question

  • Why doesn't this work?

    SET IDENTITY_INSERT TemoCatalogProducts ON
    INSERT INTO TempCatalogProducts
    SELECT * FROM CatalogProducts WHERE VariantID IN ('125244', '104907', '144083', '144098', '144099', '144097', '144096')

    I still get the error:

    Msg 8101, Level 16, State 1, Line 2
    An explicit value for the identity column in table 'TempCatalogProducts' can only be specified when a column list is used and IDENTITY_INSERT is ON.
    


    Kevin Burton

    Friday, January 11, 2013 4:13 PM

Answers


  • Msg 8101, Level 16, State 1, Line 2 An explicit value for the identity column in table 'TempCatalogProducts' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    The error message is pretty clear. for inserting value to  identity column two condition should satisfy
    1. INDENTITY INSERT should be set to ON
    2. Explicit column names should be specified in the query 
    Regards
    Satheesh

    • Marked as answer by KevinBurton Friday, January 11, 2013 7:11 PM
    Friday, January 11, 2013 5:28 PM

All replies

  • isn't the columns clause mandatory in this case?

    INSERT INTO TempCatalogProducts (col1, col2,...)



    Regards, Nico

    pdfaid, my blog

    • Proposed as answer by Jon Dinnes Friday, January 11, 2013 5:17 PM
    Friday, January 11, 2013 4:21 PM
  • Your first statement says "TemoCatalogProducts" and your tables is "TempCatalogProducts".
    Friday, January 11, 2013 4:22 PM
  • Hi kevin,

                in the insert statement give columns list of that table and try ,

                  here is an example ,

    create table test(id int identity(1,1),col1 varchar(20))
    set identity_insert test on
    insert test (id,col1)select 1,'no identity column'
    --drop table test


    Thanks & Regards, sathya

    Friday, January 11, 2013 4:22 PM
    Moderator
  • SET IDENTITY_INSERT TemoCatalogProducts ON
    INSERT INTO TempCatalogProducts
    SELECT * FROM CatalogProducts WHERE VariantID IN ('125244', '104907', '144083', '144098', '144096')
    SET IDENTITY_INSERT TemoCatalogProducts OFF

    Friday, January 11, 2013 4:22 PM

  • Hi Nico,

                i just tried an example , columns list should be specified to avoid the error

    "Msg 8101, Level 16, State 1, Line 2
    An explicit value for the identity column in table 'TempCatalogProducts' can only be specified when a column list is used and IDENTITY_INSERT is ON.
    "


    Thanks & Regards, sathya

    Friday, January 11, 2013 4:37 PM
    Moderator

  • Msg 8101, Level 16, State 1, Line 2 An explicit value for the identity column in table 'TempCatalogProducts' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    The error message is pretty clear. for inserting value to  identity column two condition should satisfy
    1. INDENTITY INSERT should be set to ON
    2. Explicit column names should be specified in the query 
    Regards
    Satheesh

    • Marked as answer by KevinBurton Friday, January 11, 2013 7:11 PM
    Friday, January 11, 2013 5:28 PM
  • SO you are saying that I need to specify the column names. If I specify the column names I don't need the VALUES, right?

    Kevin Burton

    Friday, January 11, 2013 7:11 PM
  • You need to write your insert this way:

    INSERT INTO TempCatalogProducts (ProductId, Column1, Column2) SELECT ProductId, Column1, Column2 FROM CatalogProducts

    WHERE VariantID IN ('125244', '104907', '144083', '144098', '144099', '144097', '144096')


    In other words, you need to specify all columns in both INSERT INTO and then in the SELECT.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, January 11, 2013 9:25 PM
    Moderator
  • very good !  thanks
    Wednesday, December 25, 2013 7:55 AM