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

            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

            Circuit construction for execution of conditional statements using least significant bitHow are two different registers being used as “control”?How exactly is the stated composite state of the two registers being produced using the $R_zz$ controlled rotations?Efficiently performing controlled rotations in HHLWould this quantum algorithm implementation work?How to prepare a superposed states of odd integers from $1$ to $sqrtN$?Why is this implementation of the order finding algorithm not working?Circuit construction for Hamiltonian simulationHow can I invert the least significant bit of a certain term of a superposed state?Implementing an oracleImplementing a controlled sum operation

            Image
            How to attach cable mounting points to a bicycle frame? Is Jon Snow the last of his House? A steel cutting sword? I know that there is a preselected candidate for a position to be filled at my department. What should I do? Is the field of q-series 'dead'? How to cut a climbing rope? Do photons bend spacetime or not? Where have Brexit voters gone? Is it legal to have an abortion in another state or abroad? Did this character show any indication of wanting to rule before S8E6? How to respond to upset student? Compaq Portable vs IBM 5155 Portable PC Is the Indo-European language family made up? Can I summon an otherworldly creature with the Gate spell without knowing its true name? USPS Back Room - Trespassing? Can my floppy disk still work without a shutter spring? Alternatives to achieve certain output format Are black holes spherical during merger? Can the product of any two aperiodic functions which are defined on the entire number line be p
            Read more

            Magento 2 “No Payment Methods” in Admin New OrderHow to integrate Paypal Express Checkout with the Magento APIMagento 1.5 - Sales > Order > edit order and shipping methods disappearAuto Invoice Check/Money Order Payment methodAdd more simple payment methods?Shipping methods not showingWhat should I do to change payment methods if changing the configuration has no effects?1.9 - No Payment Methods showing upMy Payment Methods not Showing for downloadable/virtual product when checkout?Magento2 API to access internal payment methodHow to call an existing payment methods in the registration form?

            Image
            What is the meaning of [[:space:]] in bash? Is this artwork (used in a video game) real? What happens on Day 6? Link of a singularity Should I be able to keep my company purchased standing desk when I leave my job? Alternator dying so junk car? A scene of Jimmy diversity Is the Gritty Realism variant incompatible with dungeon-based adventures? What could be reasoning of male prison in VR world to only allow undershirt and sarong as nightwear to male prisoners Why does FFmpeg choose 10+20+20 ms instead of an even 16 ms for 60 fps GIF images? Sending a photo of my bank account card to the future employer Advice for paying off student loans and auto loans now that I have my first 'real' job If I stood next to a piece of metal heated to a million degrees, but in a perfect vacuum, would I feel hot? What made Windows ME so crash-prone? Can a dragon's breath weapon pass through Leomund's Tiny Hut? Did 007 exist before James Bond? Is there a sour
            Read more