Error: An explicit value for the identity column in table 'Customer' - SQL Server

Question

Help me with my problem. I always receive an error whenever I run my script

Here is the code:

SET ANSI_WARNINGS OFF; 
GO

IF OBJECT_ID('IISI_tmpMergeCustTable123', 'U') IS NOT NULL
    DROP TABLE IISI_tmpMergeCustTable123

select  FirstName as FirstName
       ,LastName as LastName
       , max(AccountNumber) as AccountNumber
       , max(AccounttypeID) as  AccounttypeID
       , max(Address2) as Address2
       , MAX(CASE WHEN AssessFinanceCharges = 1 THEN 1 ELSE 0 END) as AssessFinanceCharges
       , max(Company) as Company

INTO IISI_tmpMergeCustTable123 
from CustomerBackUp
group by FirstName, LastName

TRUNCATE TABLE Customer

SET IDENTITY_INSERT Customer ON;
GO
INSERT INTO Customer
SELECT * FROM IISI_tmpMergeCustTable123
GO
SET IDENTITY_INSERT Customer OFF 
GO

Complete Error Message:

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'Customer' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Error Message always point to:

INSERT INTO Customer

Thanks.

I think you have to look at `a column list is used`, i.e. you have explicitly name every single field of `Customer` table. — Giorgos Betsos, Oct 23 '15 at 06:26
Possible duplicate of [An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY\_INSERT is ON SQL Server](http://stackoverflow.com/questions/2005437/an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a) — Giorgos Betsos, Oct 23 '15 at 06:32
By the way, How can I insert DBtimestamp, one my column has a timestamp then I cannot insert it to Customer table from IISI_tmpMergeCustTable123 — Edeliza Calago, Oct 23 '15 at 07:00

Stanislovas Kalašnikovas · Answer 1 · 2018-07-27T05:48:04.730

2

Try to specify column list in following:

SET IDENTITY_INSERT Customer ON;
GO
INSERT INTO Customer (col1, col2...) -- try to specify column list here
SELECT col1, col2... 
FROM IISI_tmpMergeCustTable123

edited Jul 27 '18 at 05:48

answered Oct 23 '15 at 06:27

Stanislovas Kalašnikovas

9,203
4
32
59

score 0 · Answer 2 · answered Oct 23 '15 at 06:27

just an example..use your columns of your table 
SET IDENTITY_INSERT Customer ON;

INSERT INTO Customer (col1,col2,col3......)
SELECT col1,col2,col3,...... FROM IISI_tmpMergeCustTable123

SET IDENTITY_INSERT Customer OFF 
GO

You have to make a column list for your INSERT statement

not like "INSERT Into Customer SELECT ........"

score 0 · Answer 3 · answered Oct 23 '15 at 06:29

SET IDENTITY_INSERT Customer ON
GO

-- You have to make a column list for your INSERT statement:
INSERT INTO Customer ([id], [c2], [c3], [c4], [c5] ) 
SELECT [id], [c2], [c3], [c4], [c5] FROM IISI_tmpMergeCustTable123
--not like "INSERT Into Customer SELECT ........"

SET IDENTITY_INSERT tableA OFF
GO

Error: An explicit value for the identity column in table 'Customer' - SQL Server

3 Answers3