Hi all,
I am trying to use bulk insert and to overwrite the primary keys in the table(destination) with the ones in the text file (source), I am setting the MAxErrors option to 9999, but I am always getting the following error:
"Violation of PRIMARY KEY constraint 'PK_Testingbulk'. Cannot insert duplicate key in object 'Testingbulk'.
The statement has been terminated."

I dont want this statment to be cancelled even if there is errors, and i think maxerror option is the way to do that but its not working.

thanx in advance.
Regards,

How are you running the insert? Are you setting the KEEPIDENTITY flag?



--

Joe

Hi,
there is no identity columns in the table the only constraint is an integer primary key, and I want to replace the whole row of a primary key in the table with the same primary key coming from the text file, the insert is being called from a VB.Net code when a file is generated.

Regards,

As I understand it MaxErrors only means the procedure carries on after errors, it doesn't let you break constraints. Can you show your BULK INSERT query and your source and target table structure?

--

Joe

As I know also if I have a duplicate key it should be considered as error and it should be skipped and the statment should complete normally, I am testing it on a simple table with 2 columns integer(PK)and nvarchar(2), my statment is
"BULK INSERT test.dbo.[testingbulk]
   FROM 'c:\bulk.txt'
   WITH
      ( datafiletype = 'char',
         FIELDTERMINATOR = ',',
         maxerrors = 1000,
     KEEPIDENTITY
      )"

thanx,

Jus read the documentation again, seems MAXERRORS does not apply to constraint checks. I'll try to experiment a bit more.

--

Joe

i think you have to remove temperory pk constraint , and all other constraints to avoid all errors and for fast copying and check for these constraints after copying, and redo relations.

Ahmed Ali
Software Developer