April 25, 2013 at 4:28 am
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/
April 25, 2013 at 4:31 am
Do you not just have to set the checkbox 'Enable Identity Insert' in the Column mappings dialog box?
April 25, 2013 at 7:17 am
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
April 25, 2013 at 7:19 am
IgorMi (4/25/2013)
HiTry 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/
April 25, 2013 at 7:22 am
Why is your ROWTERMINATOR = '', shouldn't it be "back slash n"?
Igor Micev,My blog: www.igormicev.com
April 25, 2013 at 7:24 am
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/
April 25, 2013 at 7:27 am
Then it seems very likely you have unexpected '' in your data somewhere.
Igor Micev,My blog: www.igormicev.com
April 25, 2013 at 8:09 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy