Identity inserts with view

IDENTITY_INSERT works through a view only if the view is a one-to-one projection of a single base table. SQL Server simply forwards the insert to the base table.

Normal view we can use identity insert this view must be:

Ø  Based on one table only

Ø  No JOIN

Ø  No GROUP BY

Ø  No DISTINCT

Ø  No computed columns

Ø  No aggregates

Ø  No subqueries

Ø  Not an indexed view

Apart form that in other view we can’t use this.

Read : https://bageshkumarbagi-msbi.blogspot.com/2017/03/identity-in-sql-server.html

https://bageshkumarbagi-msbi.blogspot.com/2025/11/keep-identity-table-hint-in-sql-server.html

 Let’s see the demo.

Creating a table which has identity column.

CREATE TABLE dbo.Employee (     EmpID INT IDENTITY(1,1) PRIMARY KEY,     EmpName VARCHAR(100) not null,               DeptID INT not null ); GO  CREATE TABLE dbo.Dept (     DeptID INT IDENTITY(1,1) PRIMARY KEY,     DeptName VARCHAR(100) not null ); GO insert into dbo.Dept(DeptName) values ('IT'),('HR'),('Admin'),('Finance') insert into dbo.Employee(EmpName,DeptID) values ('Bagesh',1)

Table created and inserted few records into them.

Now creating a normal view.

CREATE VIEW dbo.vEmployee AS SELECT EmpID, EmpName,DeptID FROM dbo.Employee; GO

See the data into view.

Inserting a record using the view.

insert into dbo.vEmployee(EmpName,DeptID) values ('Rajesh',2); insert into dbo.vEmployee(EmpName,DeptID) values ('Ganesh',3);

See the data

See here ID is generated automatically.

Now creating a complex view which has join and see.

CREATE VIEW dbo.vEmployeedetails AS SELECT e.EmpID, e.EmpName, d.DeptID FROM dbo.Employee e JOIN dbo.Dept d ON e.DeptID = d.DeptID;

 Now inserting record using this view.

insert into dbo.vEmployeedetails(EmpName,DeptID) values ('Mahesh',3);

Getting an error.