how to capture all row errors on bulk insert.

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

SQL Server 2008 Forums

Transact-SQL (2008)

how to capture all row errors on bulk insert.

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-16 : 08:56:13

Hi. So far i can capture the overall error of a bulk insert.


Truncate table [dbo].[dbfile];
Begin Try
begin
    
BULK INSERT [dbo].[dbfile]
FROM 'C:\works\databasebulks\test.txt'
WITH (MAXERRORS = 500000,LASTROW = 100, --CODEPAGE ='ACP',  
--CODEPAGE = '65001',
--CODEPAGE ='1252',
--CODEPAGE ='RAW',
FORMATFILE='C:\work\databasebulks\dbxmlformatfiletests.xml'); 
end
End Try
Begin CATCH
begin
INSERT INTO [dbo].DBInsertErrors
(ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorMessage, DateTimeStamp)
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
'CSV File Bulk_Insert: ' + ERROR_MESSAGE() as ErrorMessage,
GetDate() as DateTimeStamp;
Print('** Bulk Insert Process error: see table dbo.ProcessCSV_Error for details.**')
end
end catch

This will give an overall error on only one row.
I was wondering if it is possible to capture the row errors. What i mean is that if only run this portion:


BULK INSERT [dbo].[dbfile]
FROM 'C:\works\databasebulks\test.txt'
WITH (MAXERRORS = 500000,LASTROW = 100,  
FORMATFILE='C:\work\databasebulks\dbxmlformatfiletests.xml');

It will start, for example, complaining that line 3 is not well formatted, line 8 codepage is wrong,etc. HOWEVER, it will continue the validation and insert lines since the MAXERRORS i set, let it slide. I would like though to be able to record these exceptions, even though they will not affect the final bulk insert.
Thanks.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-16 : 09:20:24

you can use the error file option and then if you really want the errors in a table - write a script to load the error file.