identity insert

Question

Post reply

identity insert

kapil_kk

SSC-Insane

Points: 21316
More actions
April 25, 2013 at 4:28 am

#277018

Hi, when I am insrting data from a csv file I am getting error:
'Explicit value must be specified for identity column in table 'ABC' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
'
CREATE TABLE ABC
(ID [int] IDENTITY(1,1) NOT NULL, -- primary key
aa [varchar](50) NULL,
bb [varchar](50) NULL,
cc [datetime] NULL,
dd [varchar](50) NULL,
ee [varchar](50) NULL,
ff [int] NULL,
gg [varchar](50) NULL,
ii [int] NULL,
jj [int] NULL)
SET IDENTITY_INSERT ABC on
BULK
INSERT ABC
FROM 'D:\test.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
data in csv file is like:
84,0b0dbe1d,192.168.10.221,15:41.5,0b0dbe1d_16-15-18,1.0.0,2,pra,2,NULL
85,111de4b6,192.168.10.221,27:06.1,111de4b6_16-27-05,1.0.0,8,Diane,5,NULL
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

davidgray SSC Eights! Points: 860 More actions · Answer 1

Do you not just have to set the checkbox 'Enable Identity Insert' in the Column mappings dialog box?

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

Hi

Try to do that with KEEPIDENTITY option in the WITH() section of the BULK command. See this http://msdn.microsoft.com/en-us/library/ms186335.aspx

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

IgorMi (4/25/2013)
Hi
Try to do that with KEEPIDENTITY option in the WITH() section of the BULK command. See this http://msdn.microsoft.com/en-us/library/ms186335.aspx
Regards,
IgorMi

I tried that but still error was coming

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Why is your ROWTERMINATOR = '', shouldn't it be "back slash n"?

Igor Micev,My blog: www.igormicev.com

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

IgorMi (4/25/2013)
Why is your ROWTERMINATOR = '', shouldn't it be "back slash n"?

my ROWTERMINATOR is ''

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 6

Then it seems very likely you have unexpected '' in your data somewhere.

Igor Micev,My blog: www.igormicev.com

Lowell SSC Guru Points: 323504 More actions · Answer 7

remove the SET IDENTITY_INSER

%T command. since it is not valid/has no effect with BULK INSERT statements.

instead, as identified, you need to add KEEPIDENTITY to your BULK INSERT command:

BULK

INSERT ABC

FROM 'D:\test.csv'

WITH

(

KEEPIDENTITY ,

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

GO

.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!