Skip to content
Fix Code Error

How to turn IDENTITY_INSERT on and off using SQL Server 2008?

March 13, 2021 by Code Error
Posted By: Anonymous

Why am I getting an error doing an insert when IDENTITY_INSERT is set to OFF?

How do I turn it on properly in SQL Server 2008? Is it by using SQL Server Management Studio?

I have run this query:

SET IDENTITY_INSERT Database. dbo. Baskets ON

Then I got the message back in the console that the Command(s) completed successfully.
However when I run the application, it still gives me the error shown below:

Cannot insert explicit value for identity column in table 'Baskets' when 
IDENTITY_INSERT is set to OFF.

Solution

Via SQL as per MSDN

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT INTO sometableWithIdentity 
    (IdentityColumn, col2, col3, ...)
VALUES 
    (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF

The complete error message tells you exactly what is wrong…

Cannot insert explicit value for identity column in table ‘sometableWithIdentity’ when IDENTITY_INSERT is set to OFF.

Answered By: Anonymous

Related Articles

  • exclude items based on field value
  • How do I include certain conditions in SQL Count
  • ClassNotFoundException:…
  • An explicit value for the identity column in table…
  • Is it possible to have multiple joins between two…
  • SQL Server: Query fast, but slow from procedure
  • How to get distinct count of records from table…
  • SQL query return data from multiple tables
  • How to create a temporary table in SSIS control flow…
  • Conditional WHERE clauses with TVP parameter
  • How do I obtain a Query Execution Plan in SQL Server?
  • OpenIddict Roles/Policy returns 403 Forbidden
  • Form field border-radius is not working only on the…
  • Adding an identity to an existing column
  • T-SQL: Can I query one view for values and then…
  • Obtain most recent value for based on index in a…
  • Can a "User Assigned Managed Identity" be used locally?
  • Set IDENTITY_INSERT ON is not working
  • How to call Stored Procedure in a View?
  • Improve INSERT-per-second performance of SQLite
  • How to update Identity Column in SQL Server?
  • How should a model be structured in MVC?
  • SQL Server, How to set auto increment after creating…
  • T-SQL How to create tables dynamically in stored procedures?
  • IDENTITY_INSERT is set to OFF - How to turn it ON?
  • C# group by a list contains another class
  • data.table vs dplyr: can one do something well the…
  • Vue JS - How to do conditional display of text based…
  • How can I find the product GUID of an installed MSI setup?
  • Generate SQL Create Scripts for existing tables with Query
  • python 3.2 UnicodeEncodeError: 'charmap' codec can't…
  • How can I drop a table if there is a foreign key…
  • How to Update Database from Assets Folder in App
  • Passing DataTable to Table-Valued Parameter in…
  • Circular Dependency with two depending Services
  • What are all the user accounts for IIS/ASP.NET and…
  • Add unique constraint to combination of two columns
  • Use stored procedure to insert some data into a table
  • Database development mistakes made by application developers
  • Adding a guideline to the editor in Visual Studio
  • Fastest way to update 120 Million records
  • render function or template not defined in…
  • Sort table rows In Bootstrap
  • Edit item description in array in Vue JS
  • ember: understand errors
  • NPM script to build typescript not working
  • SMTP error 554
  • 'parcel' is not recognized as an internal or…
  • Use SQL Server Management Studio to connect remotely…
  • Angular: Can't find Promise, Map, Set and Iterator
  • What is the difference between Scope_Identity(),…
  • Confused about UPDLOCK, HOLDLOCK
  • Port 3000 is already in use Error every time I…
  • ASP.NET MVC 5 - Identity. How to get current ApplicationUser
  • Foreign Key to multiple tables
  • I want to create a SQLite database like in the…
  • Adding a new column into sqlite produces an error:…
  • Logging best practices
  • How to Detect if I'm Compiling Code with a…
  • How to change identity column values programmatically?
  • Procedure or function !!! has too many arguments specified
  • How to find if one of the items have completed…
  • DROP Constraint without knowing the name
  • 3 column layout HTML/CSS
  • AngularJS ui-router login authentication
  • Reduce vs Collect method on Parallel Streams vs…
  • SQL Insert Query Using C#
  • How to access a DOM element in React? What is the…
  • Best way to get identity of inserted row?
  • SQL Server stored procedure Nullable parameter
  • 'Incorrect SET Options' Error When Building Database Project
  • Iron-data-table fails to populate data from databinding
  • operator is treated as a variable in SQL
  • Ukkonen's suffix tree algorithm in plain English
  • Why can't I find SQL Server Management Studio after…
  • Insert multiple values using INSERT INTO (SQL Server 2005)
  • Why cat does not work with parameter -0 in xargs?
  • How can I fix MySQL error #1064?
  • Simple C example of doing an HTTP POST and consuming…
  • How to get rid of this problem ( On adding a nuget…
  • Create a view with ORDER BY clause
  • Keycloak/Wildfly How to configure all console logs…
  • How to install SQL Server Management Studio 2008…
  • Could not load file or assembly…
  • Best way to do multi-row insert in Oracle?
  • Checkbox: how to cross out text one by one and not…
  • Unity Scripts edited in Visual studio don't provide…
  • Smart way to truncate long strings
  • const commandFolders = readdirSync('./commands');…
  • Best way to replace multiple characters in a string?
  • Change completed to true on button click in Vuejs 2
  • Enforce additional constraints on N:M table server
  • What are the undocumented features and limitations…
  • SQL Server reports 'Invalid column name', but the…
  • Operand type clash: int is incompatible with date +…
  • SQL Server SELECT into existing table
  • How to activate JMX on my JVM for access with jconsole?
  • Individual click handlers in v-for loop
  • How to get the in HTML tables to fit content, and…
  • SQL Server Trigger on Specified Columns

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

How to remove text from a string?

Next Post:

How do I call a JavaScript function on page load?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error