IDENTITY_INSERT is set to OFF/ ON using SQL SERVER

In this Article introduce about IDENTITY_INSERT ON/OFF in SQL Server Discuss in detail with example see bleow discription in this Article

What is IDENTITY_INSERT

IDENTITY_INSERT When you create table in database then one field is ID then this field is integer type and autoincrement if yuow will set Identity(i,1), when you create table then insert any row then Identity column will autometic incremet by 1 this is the time IDENTITY_INSERT is OFF means you can't insert manualy. Means in insert query You can't insert id in table in example Follow some steps-
1st Steps - Create Data Base and Create Table in Sql Server like

CREATE TABLE Tbl_Identity
(
Id INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(100)
)
Note- This is syntax of Create table and execute run F5 in sql server

2nd Steps - After Create table You can insert data in table like in example

INSERT INTO Tbl_Identity (Id, Name) VALUES (1, 'Testing')
Note - Write Insert query and execute this query then you find a error

Error show like this-

Msg 544, Level 16, State 1, Line 6 Cannot insert explicit value for identity column in table 'Tbl_Identity' when IDENTITY_INSERT is set to OFF.

Create table and set Id int identity then id column is autometicaly incremented by 1 that the resion if you insert any row then id field is automatic incremented, in this previous example you insert any record in table then you find error when you execute and run this query because the IDENTITY_INSERT is OFF On Tbl_Identity That the resion you can't enter any new id manully...

So first of all You Set IDENTITY_INSERT ON

Syntex - Set IDENTITY_INSERT TableName ON
Example - Set IDENTITY_INSERT Tbl_Identity ON

Note - Write this syntex and execute

Set IDENTITY_INSERT ON Execute after that You can Insert query and pass id field manully using insert query and you find record is inserted successfully,Untill unless you can not insert id manully.
Note - If Table have ID field is autoincremented and You want to insert Id manually then First of all You should Set IDENTITY_INSERT [TableName] ON ,Run this query after you can insert and if you set Off then you can't insert id manually, Syntex - Set IDENTITY_INSERT [TableName] OFF , Run this query so identity Off

Set IDENTITY_INSERT ON/OFF using LINQ.

Insert identity on table you can change Id values in table through linq query Following some steps like -

• 1st Step - Establish Connection (connect database in your project).

DummyShoppingEntities db = new DummyShoppingEntities();
Add entity in your project through add edmx file and automatic created connection string in web config file like this.
Ex. -Show in Web.Config file-
  <connectionStrings>
  <add name="DummyShoppingEntities" connectionString="metadata=res://*/Models.Dummy_DB.csdl|res://*/Models.Dummy_DB.ssdl|res://*/Models.Dummy_DB.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=DESKTOP-GFUF27H;initial catalog=Db_DummyShopping;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
Note-
name="DummyShoppingEntities" add name for access database in mvc

• 2nd Step - Write query in linq.

Create Object of entity and write queryin sql in example-
public ActionResult Index()
{
 db.Database.ExecuteSqlCommand("Set IDENTITY_INSERT TableName ON;INSERT INTO    TableName (ColumnName, ColumnName) VALUES (ColumnValue, 'ColumnValue');Set  IDENTITY_INSERT Tbl_Identity TableName ");
db.SaveChanges();
 return View();
}
Example
public ActionResult Index()
{
db.Database.ExecuteSqlCommand("Set IDENTITY_INSERT Tbl_Identity ON;INSERT INTO Tbl_Identity (Id, Name) VALUES (2, 'Testing');Set IDENTITY_INSERT Tbl_Identity OFF ");
db.SaveChanges();
return View();
}

Note - In this example IDENTITY_INSERT on on table and write query in insert record in table and IDENTITY_INSERT off, Tbl_Identity is a table name

You can change another method also like

Example
public ActionResult Index()
{
Tbl_Identity item = new Tbl_Identity();
item.Id = 12;
item.Name = "";
db.Database.ExecuteSqlCommand("Set IDENTITY_INSERT Tbl_Identity ON;INSERT INTO Tbl_Identity (Id, Name) VALUES ("+ item.Id + ", '"+ item.Name+ "');Set IDENTITY_INSERT Tbl_Identity OFF ");
OR Add parameter also
db.Database.ExecuteSqlCommand("Set IDENTtestITY_INSERT Tbl_Identity ON;INSERT INTO Tbl_Identity (Id, Name) VALUES ({0},{1});Set IDENTITY_INSERT Tbl_Identity OFF,item.Id,item.Name ");
db.SaveChanges();
return View();
}

Note - Tbl_Identity is table name and you can define values in table field also

you can also use sql connection and add parameter like ado.net show in example like-

Example -
public ActionResult Index()
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["test"].ConnectionString);
SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = con;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
ModelClass item = new ModelClass();
item.Id = 12;
item.Name = "test";
string query = "INSERT INTO Tbl_Identity (Id, Name) VALUES (@id,'" + item.Id + "', @name,'" + item.Name + "')";
sqlcom.Parameters.AddWithValue("id", item.Id);
sqlcom.Parameters.AddWithValue("id", item.Name);
db.Database.ExecuteSqlCommand("Set IDENTITY_INSERT Tbl_Identity ON;" + query + ";Set IDENTITY_INSERT Tbl_Identity OFF ");
db.SaveChanges();
con.Close();
return View();
}

Note - In this example firstly you establish a connection using sqlconnection and command after that open connection and declare model class and define properties.in this example class is "ModelClass" and set values then add parameter.Execute command identity insert on and insert record and identity off.

• 3rd Steps - Work on Identity_Insert on and insert record theen off and save changes through linq. If u try this the you can easly insert id in table and update also.

Reset Identity Column in Sql Server

What is Reset Identity ?

Resest Identity - In Sql server whenever you create a table you define primary key identity means autometic increment by 1 when ever you insert record in table then so identity is autoincrement,Incase You want to change and reset identity field that is Reset Identity

Why Need to Reset Identity Column in sqlServer ?

In This artical we discuss about Reset identity column in table.Sometime you want to Change identity field in table like
Ex.
If you create a table and insert some record and you want to delete some record and again insert record then you find new inserted record those id number is different because Deleted record id number is skip. So you want to change identity field in table and maintain sequence then use Reset Identity.This more clear by Example see below example -

Example-

SELECT * FROM vibha1<Table Name> --Here Vibha1 is TableName

We get Values-

Delete data  from table

Ex. 

DELETE FROM vibha1 WHERE id=6
One Value Delete then Insert Value 

INSERT INTO vibha1(name) VALUES('vibha1')

Get Values-

Then We Use Code

Syntax: DBCC CHECKIDENT( <TableName>, RESEED, <ValuesThen Start>) 

And Delete id 7 record

DELETE FROM vibha1 WHERE id=7
Then  Execute This

 DBCC CHECKIDENT( vibha1 , RESEED, 5)
Then Insert

INSERT INTO vibha1(name) VALUES('vibha1')

Now Get Result