SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Bulk Insert Ignores Maxerrors and Errorfile
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ejaggers
Starting Member

16 Posts

Posted - 02/14/2012 :  17:30:46  Show Profile  Reply with Quote
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 - 02/14/2012 :  21:56:58  Show Profile  Reply with Quote
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/


Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 02/15/2012 :  07:26:01  Show Profile  Visit russell's Homepage  Reply with Quote
Straight from BOL:

"The MAXERRORS option does not apply to constraint checking."
Go to Top of Page

ejaggers
Starting Member

16 Posts

Posted - 02/15/2012 :  09:24:56  Show Profile  Reply with Quote
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!!!!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/15/2012 :  09:53:57  Show Profile  Reply with Quote
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/



Edited by - X002548 on 02/15/2012 09:54:28
Go to Top of Page

ejaggers
Starting Member

16 Posts

Posted - 02/15/2012 :  10:58:40  Show Profile  Reply with Quote

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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/15/2012 :  11:09:02  Show Profile  Reply with Quote
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/


Go to Top of Page

ejaggers
Starting Member

16 Posts

Posted - 02/15/2012 :  12:05:23  Show Profile  Reply with Quote
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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/15/2012 :  12:08:27  Show Profile  Reply with Quote
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.





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/


Go to Top of Page

ejaggers
Starting Member

16 Posts

Posted - 02/15/2012 :  12:22:10  Show Profile  Reply with Quote
Right, That's what I also saw, but what type errors for instance??? Bad date maybe? 02/31/2012???
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/15/2012 :  12:24:52  Show Profile  Reply with Quote
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/


Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 02/15/2012 :  12:36:30  Show Profile  Visit russell's Homepage  Reply with Quote
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.
Go to Top of Page

ejaggers
Starting Member

16 Posts

Posted - 02/15/2012 :  13:38:19  Show Profile  Reply with Quote
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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/15/2012 :  13:55:42  Show Profile  Reply with Quote
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/


Go to Top of Page

ejaggers
Starting Member

16 Posts

Posted - 02/15/2012 :  13:56:48  Show Profile  Reply with Quote
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.
Go to Top of Page

ejaggers
Starting Member

16 Posts

Posted - 02/15/2012 :  14:04:08  Show Profile  Reply with Quote
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???
Go to Top of Page

ejaggers
Starting Member

16 Posts

Posted - 02/15/2012 :  14:19:31  Show Profile  Reply with Quote
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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/15/2012 :  14:38:07  Show Profile  Reply with Quote
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/


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000