在批量插入操作时如何捕获包含错误数据的行？ [英] How do I catch the line with error data while bulk insert operation?

问题描述

My csv file:

empNo | empName
one | John 
2 | Robin 
3 | Thomas

since empNo is not in correct data type and it will not insert in stage table but rest do, Is there any way i can able to catch which line has error data

我的代码

My code

Try

           sSQL = " BULK INSERT EmpStageTable"
           sSQL += " FROM '" + FullPath + "' "
           sSQL += " WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' ) "
           sSQL += " ;select count(*) from EmpStageTable"

           Using cmd As SqlCommand = New SqlCommand(sSQL, cn)
               cmd.ExecuteScalar()
           End Using

我的尝试：



我没有想法怎么做。

What I have tried:

I have no idea how to do it.

I tried to count successful records and total records to find out number of records failed.. but it did not work in all scenarios. 

推荐答案

不，没有迹象表明哪一行出错：您必须自己寻找并找到它。或者更好的是，自己读取数据，将其处理为有效数据，并将其作为数据表或类似数据传递： SqlBulkCopy.WriteToServer方法（DataTable）（System.Data.SqlClient） [ ^ ]

No, there isn't an indication of which row is at fault: you would have to look for yourself and find it. Or better, read the data yourself, process it into valid data only, and pass it as a datatable or similar: SqlBulkCopy.WriteToServer Method (DataTable) (System.Data.SqlClient)[^]

这篇关于在批量插入操作时如何捕获包含错误数据的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！