CODEWITHSUNDEEP
sql-server
Edeliza Calago
Edeliza Calago

Reputation: 41

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

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.

Upvotes: 1

Views: 701

Answers (3)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

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

Upvotes: 2

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

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

Upvotes: 0

mohan111
mohan111

Reputation: 8865

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 ........"

Upvotes: 0

Related Questions