Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON in SQL Server

When you got below error while executing below SQL Statement

INSERT INTO dbo.tlbTargetTable SELECT * FROM dbo.tlbSrcTable

Msg 8101, Level 16, State 1, Line 19

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

SET IDENTITY_INSERT dbo.tlbTargetTable ON

Go

INSERT INTO dbo.tlbTargetTable SELECT * FROM dbo.tlbSrcTable

Go

SET IDENTITY_INSERT dbo.tlbTargetTable OFF

Go

You will get below error message after switching on identity insert

Msg 8101, Level 16, State 1, Line 20

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

You have to add column list for insert statement

SET IDENTITY_INSERT dbo.tlbTargetTable ON

Go

INSERT INTO dbo.tlbTargetTable (id, col1, col2, col3, col4, col5) SELECT id, col1, col2, col3, col4, col5 FROM dbo.tlbSrcTable

Go

SET IDENTITY_INSERT dbo.tlbTargetTable OFF

Go