Why am I receiving the identity insert error even after explicitly setting IDENTITY_INSERT ON and using a column list? [on hold]IDENTITY_INSERT seems stuckSQL Server 2008 R2 Transactional Replication “Cannot insert explicit value for identity column…”How to set identity_insert to on in SQL ServerUpdate statement is giving an invalid column name error, even though the column existsWhy doesn't SQL Server rollback the identity_insert state?Unable to insert explicit value in table due to IDENTITY_INSERT is ON ErrorChange IDENTITY_INSERT to ON in SQL serverWhy can a SQL Server table not have more than one IDENTITY column?Generated bug data script how not to set the IDENTITY in the insert statements?Use Multiple Expressions In Select List

Game artist computer workstation set-up – is this overkill?

Extracting the parent, leaf, and extension from a valid path

Why is there a cap on 401k contributions?

Is there a reason why Turkey took the Balkan territories of the Ottoman Empire, instead of Greece or another of the Balkan states?

Assuming a normal distribution: what is the sd for a given mean?

Gift for mentor after his thesis defense?

Are modes in jazz primarily a melody thing?

How to make a kid's bike easier to pedal

How does jetBlue determine its boarding order?

The unknown and unexplained in science fiction

Bash prompt takes only the first word of a hostname before the dot

Scaling rounded rectangles in Illustrator

How can I draw a rectangle around venn Diagrams?

What’s the interaction between darkvision and the Eagle Aspect of the beast, if you have Darkvision past 100 feet?

Why always 4...dxc6 and not 4...bxc6 in the Ruy Lopez Exchange?

Does this website provide consistent translation into Wookiee?

Is throwing dice a stochastic or a deterministic process?

All of my Firefox add-ons have been disabled suddenly, how can I re-enable them?

LiOH hydrolysis of methyl 2,2-dimethoxyacetate not giving product?

Make me a minimum magic sum

Why was Gemini VIII terminated after recovering from the OAMS thruster failure?

Concatenate all values of the same XML element using XPath/XQuery

What's the difference between "ricochet" and "bounce"?

Function annotation with two or more return parameters



Why am I receiving the identity insert error even after explicitly setting IDENTITY_INSERT ON and using a column list? [on hold]


IDENTITY_INSERT seems stuckSQL Server 2008 R2 Transactional Replication “Cannot insert explicit value for identity column…”How to set identity_insert to on in SQL ServerUpdate statement is giving an invalid column name error, even though the column existsWhy doesn't SQL Server rollback the identity_insert state?Unable to insert explicit value in table due to IDENTITY_INSERT is ON ErrorChange IDENTITY_INSERT to ON in SQL serverWhy can a SQL Server table not have more than one IDENTITY column?Generated bug data script how not to set the IDENTITY in the insert statements?Use Multiple Expressions In Select List






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








0















My code:



SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn
SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF


The error:




Msg 8101, Level 16, State 1, Line 4



An explicit value for the identity column in table
'SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn' can only be specified when a column list
is used and IDENTITY_INSERT is ON.




What gives?






sql-server sql-server-2008-r2 insert table identity







share|improve this question













put on hold as off-topic by LowlyDBA, MDCCL, Colin 't Hart, Andriy M, Vérace yesterday


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, MDCCL, Colin 't Hart, Andriy M, Vérace
If this question can be reworded to fit the rules in the help center, please edit the question.
















  • Isn't this the column list (or am I being stupid? lol): SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable

    – J.D.
    May 3 at 16:16


















0















My code:



SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn
SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF


The error:




Msg 8101, Level 16, State 1, Line 4



An explicit value for the identity column in table
'SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn' can only be specified when a column list
is used and IDENTITY_INSERT is ON.




What gives?






sql-server sql-server-2008-r2 insert table identity







share|improve this question













put on hold as off-topic by LowlyDBA, MDCCL, Colin 't Hart, Andriy M, Vérace yesterday


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, MDCCL, Colin 't Hart, Andriy M, Vérace
If this question can be reworded to fit the rules in the help center, please edit the question.
















  • Isn't this the column list (or am I being stupid? lol): SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable

    – J.D.
    May 3 at 16:16














0












0








0








My code:



SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn
SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF


The error:




Msg 8101, Level 16, State 1, Line 4



An explicit value for the identity column in table
'SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn' can only be specified when a column list
is used and IDENTITY_INSERT is ON.




What gives?






sql-server sql-server-2008-r2 insert table identity







share|improve this question














My code:



SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn
SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF


The error:




Msg 8101, Level 16, State 1, Line 4



An explicit value for the identity column in table
'SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn' can only be specified when a column list
is used and IDENTITY_INSERT is ON.




What gives?






sql-server sql-server-2008-r2 insert table identity




sql-server sql-server-2008-r2 insert table identity






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked May 3 at 16:08









J.D.J.D.

568414




568414




put on hold as off-topic by LowlyDBA, MDCCL, Colin 't Hart, Andriy M, Vérace yesterday


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, MDCCL, Colin 't Hart, Andriy M, Vérace
If this question can be reworded to fit the rules in the help center, please edit the question.







put on hold as off-topic by LowlyDBA, MDCCL, Colin 't Hart, Andriy M, Vérace yesterday


This question appears to be off-topic. The users who voted to close gave this specific reason:


  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, MDCCL, Colin 't Hart, Andriy M, Vérace
If this question can be reworded to fit the rules in the help center, please edit the question.












  • Isn't this the column list (or am I being stupid? lol): SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable

    – J.D.
    May 3 at 16:16


















  • Isn't this the column list (or am I being stupid? lol): SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable

    – J.D.
    May 3 at 16:16

















Isn't this the column list (or am I being stupid? lol): SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable

– J.D.
May 3 at 16:16






Isn't this the column list (or am I being stupid? lol): SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable

– J.D.
May 3 at 16:16











1 Answer
1






active

oldest

votes


















6














Add a column list to your code:



SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn 
 (ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable)
SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF

GO


Basically, a column list is when you explicitly state what columns you're inserting into.






share|improve this answer





























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    6














    Add a column list to your code:



    SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

    INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn 
     (ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable)
    SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
    FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

    SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF

    GO


    Basically, a column list is when you explicitly state what columns you're inserting into.






    share|improve this answer



























      6














      Add a column list to your code:



      SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

      INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn 
       (ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable)
      SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
      FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

      SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF

      GO


      Basically, a column list is when you explicitly state what columns you're inserting into.






      share|improve this answer

























        6












        6








        6







        Add a column list to your code:



        SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

        INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn 
         (ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable)
        SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
        FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

        SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF

        GO


        Basically, a column list is when you explicitly state what columns you're inserting into.






        share|improve this answer













        Add a column list to your code:



        SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

        INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn 
         (ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable)
        SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
        FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

        SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF

        GO


        Basically, a column list is when you explicitly state what columns you're inserting into.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered May 3 at 16:19









        CowthulhuCowthulhu

        22318




        22318













            -

            Popular posts from this blog

            Get product attribute by attribute group code in magento 2get product attribute by product attribute group in magento 2Magento 2 Log Bundle Product Data in List Page?How to get all product attribute of a attribute group of Default attribute set?Magento 2.1 Create a filter in the product grid by new attributeMagento 2 : Get Product Attribute values By GroupMagento 2 How to get all existing values for one attributeMagento 2 get custom attribute of a single product inside a pluginMagento 2.3 How to get all the Multi Source Inventory (MSI) locations collection in custom module?Magento2: how to develop rest API to get new productsGet product attribute by attribute group code ( [attribute_group_code] ) in magento 2

            Image
            Is it safe to keep the GPU on 100% utilization for a very long time? What happens when the drag force exceeds the weight of an object falling into earth? shebang or not shebang The unknown and unexplained in science fiction Are wands in any sort of book going to be too much like Harry Potter? How do I minimise waste on a flight? Concatenate all values of the same XML element using XPath/XQuery Did any early RISC OS precursor run on the BBC Micro? Single supply non-inverting amplifier using op amp Employee is self-centered and affects the team negatively Why always 4...dxc6 and not 4...bxc6 in the Ruy Lopez Exchange? TikZ/PGF draw algorithm Learning how to read schematics, questions about fractional voltage in schematic How can I test a shell script in a "safe environment" to avoid harm to my computer? A♭ major 9th chord in Bach is unexpectedly dissonant/jazzy What detail can Hubble see on Mars? Drug Testing and Prescribed Medications Justific
            Read more

            Category:9 (number) SubcategoriesMedia in category "9 (number)"Navigation menuUpload mediaGND ID: 4485639-8Library of Congress authority ID: sh85091979ReasonatorScholiaStatistics

            Image
            Natural numbersSquare numbersCategories requiring diffusion Help Category:9 (number) From Wikimedia Commons, the free media repository Jump to navigation Jump to search number: 9 (nine) instance of: natural number, deficient number, square number, composite number, odd number, centered cube number, centered octagonal number and nonagonal number other integers: -8 • -4 • -2 • -1 • 0 • 1 • 2 • 3 • 4 • 5 • 6 • 7 • 8 • 9 • 10 • 11 • 12 • 13 • 14 • 15 • 16 • 17 • 18 • 19 • 20 • 21 • 22 • 23 • 24 • 25 • 26 • 27 • 28 • 29 -20 • 0 • 10 • 20 • 30 • 40 • 50 • 60 • 70 • 80 • 90 • 100 • 110 • 120 • 130 • 140 • 150 • 160 • 170 • 180 • 190 • 200 This category has become too crowded. It should list very few images directly. Files should be moved to subcategories where appropriate. New subcategories can be created. Some files need to be moved elsewhere. Search for more categories: fulltext, Main Page, topics, meta categories. Please remove or replace this tag
            Read more

            Magento 2.3: How do i solve this, Not registered handle, on custom form?How can i rewrite TierPrice Block in Magento2magento 2 captcha not rendering if I override layout xmlmain.CRITICAL: Plugin class doesn't existMagento 2 : Problem while adding custom button order view page?Magento 2.2.5: Overriding Admin Controller sales/orderMagento 2.2.5: Add, Update and Delete existing products Custom OptionsMagento 2.3 : File Upload issue in UI Component FormMagento2 Not registered handleHow to configured Form Builder Js in my custom magento 2.3.0 module?Magento 2.3. How to create image upload field in an admin form

            Image
            2000s (or earlier) cyberpunk novel: dystopia, mega storm, omnipresent noise How important is it for multiple POVs to run chronologically? PhD: When to quit and move on? Motorcyle Chain needs to be cleaned every time you lube it? Why do most airliners have underwing engines, while business jets have rear-mounted engines? How to supply water to a coastal desert town with no rain and no freshwater aquifers? How come a desk dictionary be abridged? What's the big deal about the Nazgûl losing their horses? Is this standard Japanese employment negotiations, or am I missing something? Should I increase my 401(k) contributions, or increase my mortgage payments Is there a way to change the aspect ratio of a DNG file? Why is there paternal, for fatherly, fraternal, for brotherly, but no similar word for sons? What can a novel do that film and TV cannot? Is conquering your neighbors to fight a greater enemy a valid strategy? /api/sitecore is not working in CD server
            Read more