Post reply

IDENTITY_INSERT ON but error about column list, even though using column list

  • February 11, 2014 at 11:11 am

    #285566

    Using [AdventureWorks2012] to perform insert which fails with error and I don't understand what to change to make insert work:

    Msg 8101, Level 16, State 1, Line 1

    An explicit value for the identity column in table 'AdventureWorks2012.Sales.SalesOrderDetail' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    --insert_on statement worked

    set identity_insert [AdventureWorks2012].[Sales].[SalesOrderDetail] ON

    GO

    --this failed.

    insert into [AdventureWorks2012].[Sales].[SalesOrderDetail]

    values

    (43665, 1, 'HkTest', 25, 715, 1, 25.00, 5.00, 25.00, 'B207C96D-D9E6-402B-8470-2CC176C42283', getdate());

  • February 11, 2014 at 11:42 am

    #1688053

    You have to add the column list to the table you're inserting into.

    insert into [AdventureWorks2012].[Sales].[SalesOrderDetail]( <Column list goes here>)

    values(43665, 1, 'HkTest', 25, 715, 1, 25.00, 5.00, 25.00, 'B207C96D-D9E6-402B-8470-2CC176C42283', getdate());

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply