Bulk Insert Ignores Maxerrors and Errorfile

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Bulk Insert Ignores Maxerrors and Errorfile

Author

Topic

ejaggers
Starting Member

16 Posts

Posted - 2012-02-14 : 17:30:46

Hi All,
I'm a new member from the UNIX world, and need help!!!

Data file reg_programs.INP contains:
57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|primeIF|
57912|145|1|999999|02/05/2012|N||01|||N|02/14/2012|primeIF|
57912|145|1|777777|02/05/2012|N||01|||N|02/14/2012|primeIF|
57912|145|1|6666663|02/05/2012|N||01|||N|02/14/2012|primeIF|

SQL:
bulk insert reg_programs from 'C:\reg_programs.INP' with (FIELDTERMINATOR='|',MAXERRORS=1000,ERRORFILE='C:\error.txt');

This sql loads all 4 records fine. But when I load the first record (to cause an error),
and run my sql again, I get:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_REG_PROGRAMS'. Cannot insert duplicate key in object 'dbo.REG_PROGRAMS'.
The statement has been terminated.

o I do not get error file 'C:\error.txt' with the duplicate record.
o Nor does it load the other 3 records.

Can some one Please tell me what I'm doing wrong?

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-14 : 21:56:58

what the DDL of the table? What's the Primary Key?

Maybe you should load it to a stage table first

And you shouldn't have a trailing delimiter unless there is a feild after it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-15 : 07:26:01

Straight from BOL:

"The MAXERRORS option does not apply to constraint checking."

ejaggers
Starting Member

16 Posts

Posted - 2012-02-15 : 09:24:56

X002548 and russell,

Thanks for your responses.

X00248 the tables looks like this:

reg_programs:
district int 4 n
program_id char 5 n
field_number smallint 2 n
student_id char 10 n
start_date datetime 8 n
summer_school char 1 n
entry_reason char 5 y
program_value varchar 255 n
end_date datetime 8 y
withdrawal_reason char 5 y
program_override char 1 n
change_date_time datetime 8 n
change_uid varchar 20 n

and I tries removing the last '|' on each record but that didn't help.

russell,

My objective is to load the complete file, but bypass any dup records and give me a list of the dups. I thought MAXERRORS and
ERRORFILE was the way to acheive this. Can you tell me a better way?

I can't afford for the whole task to fail because it's going to be a unmonitored batch job.

reg_program.bat:
sqlcmd -E -d trainDB -S sqlsever1 ^
-Q "bulk insert reg_programs from 'C:\reg_programs.INP' with (FIELDTERMINATOR='|',MAXERRORS=0,ERRORFILE='C:\error.txt');" ^
-o C:\reg_programs.log

Please Help!!!!

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 09:53:57

You don't tell what is considered a dup

What's the primary key or unique Index?

In any case, create a staging table and load that



CREATE TABLE Stage_reg_programs (
  [district]			varchar(25)		-- int 4 n
, [program_id]			varchar(255)	-- char 5 n
, [field_number]		varchar(25)		-- smallint 2 n
, [student_id]			varchar(255)	-- char 10 n
, [start_date]			varchar(25)		-- datetime 8 n
, [summer_school]		varchar(255)	-- char 1 n
, [entry_reason]		varchar(255)	-- char 5 y
, [program_value]		varchar(8000)	-- varchar 255 n
, [end_date]			varchar(25)		-- datetime 8 y
, [withdrawal_reason]	        varchar(255)	--char 5 y
, [program_override]	        varchar(255)	-- char 1 n
, [change_date_time]	        varchar(25)	-- datetime 8 n
, [change_uid]			varchar(255)	-- varchar 20 n
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ejaggers
Starting Member

16 Posts

Posted - 2012-02-15 : 10:58:40

Brett, The primary key is:

district
program_id
field_number
student_id
start_date
summer_school

What I mean by dups:

Good Data (and this works):
57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|primeIF|

Bad Data (causes an abort):
57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|primeIF|
57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|primeIF|

What I need:

load first record:
57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|primeIF|

Send second record to errorfile=error.txt and continue loading non dup records
57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|primeIF|

What is happening: it won't load the first record because of the second, and the job quits instead skipping the dup and continuing:
57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|primeIF|
57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|primeIF|

I don't understand why I need a stage table, please explain.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 11:09:02

It doesn't work that way...it doesn't load data row by row...it does it page by page...and it doesn't know which one to toss...it would be too slow

I guess you could use SSIS, but I'm not a big fan

I would

1). Load to the Staging Table I gave you..no Contraints
2). Do Data Cleaning...are dates dates, are numbers, numbers, are the sizes correct for char?
3). I would then check contraints..nullability, primary keys, foreign keys, alternate keys
4). I would then do Delta processing if the data already exists in base, and if not I would delete form the table and the do an INSERT..or bcp if the file is clean

MOO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ejaggers
Starting Member

16 Posts

Posted - 2012-02-15 : 12:05:23

Wow!!!

In the UNIX/Informix world you can do this with dbload:

dbload -d dbname -c dbload.cmd -e 1000000 -l dbload.log

The -e 1000000 says keep loading until you have reached 1000000 errors before quiting.
The -l dbload.log says log errors in file dbload.log

dbload.cmd:
FILE meal.Final DELIMITER '|' 10; # 10 columns in table
INSERT INTO tablename;

dbload.log example:
In INSERT statement number 1 of raw data file /home/uid/meal.Final Row number 519 is bad.
179365817|105467|07/12/2010|69|01|N|10/04/2010|04/01/2011|05:09:33|z2horizn|

Could not insert new row - duplicate value in a UNIQUE INDEX column (Unique Index:ix_prog_stu2).

ISAM error: duplicate value for a record with unique key.

Unix/informix has spoiled me. I'm really struggling in MS sqlserver, especially SSIS.
I'm glad there are good people on this forum, I can ask for help.

Thanks Brett

BTW, what is the MAXERRORS and ERRORFILE used for, and what is the proper way to use them? The doc is not all that clear.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 12:08:27

Which DOC? Books Online is Pretty Clear about it

quote:

MAXERRORS = max_errors
Specifies the maximum number of syntax errors allowed in the data before the bulk-import operation is canceled. Each row that cannot be imported by the bulk-import operation is ignored and counted as one error. If max_errors is not specified, the default is 10.

Note:
The MAX_ERRORS option does not apply to constraint checks or to converting money and bigint data types.

ejaggers
Starting Member

16 Posts

Posted - 2012-02-15 : 12:22:10

Right, That's what I also saw, but what type errors for instance??? Bad date maybe? 02/31/2012???

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 12:24:52

Constraints:

Primary Key
Foreign Key
Alternate Key
Column Constraint (i.e. Col1 IN ('Y','N'))
Default (but I don't think that matters)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-15 : 12:36:30

If your objective is to load the entire file, dupes and all, then you'll need to load it to a table that doesn't have a primary key -- or has a different one.

Primary Key means "Don't allow duplicates."

This is true of every RDBMS and on every platform.

ejaggers
Starting Member

16 Posts

Posted - 2012-02-15 : 13:38:19

russell, my objective is to load 4 of these 5 records without aborting:

57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|primeIF|
57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|primeIF|
57912|145|1|999999|02/05/2012|N||01|||N|02/14/2012|primeIF|
57912|145|1|777777|02/05/2012|N||01|||N|02/14/2012|primeIF|
57912|145|1|6666663|02/05/2012|N||01|||N|02/14/2012|primeIF|

And tell me the second record was a dup.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 13:55:42

What don't you get?

And again, your table has 12 columns..by having a trailing pipe makes it 13 columns

Load the data to a staging table that has 13 columns

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ejaggers
Starting Member

16 Posts

Posted - 2012-02-15 : 13:56:48

If I can replace the record in table, with the one in the file, that's even better, because the one in the file is the lastest one.

For Example:

Table record has: 57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|OLDVALUE|
File record has: 57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|newvalue|

Then something like:

replace into table using file as input.

To replace this record: 57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|OLDVALUE|
with this record: 57912|145|1|888888|02/05/2012|N||01|||N|02/14/2012|newvalue|

and no errorfile needed.

I saw a "replace into" statement on the web but can't make heads nor tails of it for my use.

ejaggers
Starting Member

16 Posts

Posted - 2012-02-15 : 14:04:08

X002548

I can load these 4 records with or without the trailing '|'. I've tried it both ways and both work, but I can take if off if
that's the proper way to use it.

What I didn't get was what type errors the MAXERRORS catches. Remember I asked was it something like this:
Bad date maybe? 02/31/2012???

ejaggers
Starting Member

16 Posts

Posted - 2012-02-15 : 14:19:31

X002548,

Okay, I got the Maxerrors thing now from playing around with it. I tried 2/31/2012 in one of the records and it loaded the other
3 records, and gave me the errorfile as well. So I'm learning.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 14:38:07

See in DB2, the errors can go to a table or a file

Here, they only go to a file

So, you then need to load the errors to an error table to pick out a row

If you load a staging table, all of the data will be there.

Then you just populate the base table with T-SQL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources