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.
| Author |
Topic |
|
Johhny Blay
Starting Member
3 Posts |
 Posted - 2015-03-31 : 08:13:58
|
| Hello, I am writing data in a csv file to sql server database, using bulk insert, I need logic around bulk insert to fail bulk insert if csv file contains bad data.Thanksjb |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-31 : 08:57:07
|
| Be sure to read:https://msdn.microsoft.com/en-CA/library/ms188365.aspxComma-separated value (CSV) files are not supported by SQL Server bulk-import operations. However, in some cases, a CSV file can be used as the data file for a bulk import of data into SQL Server. For information about the requirements for importing data from a CSV data file, seehttps://msdn.microsoft.com/en-CA/library/ms188609.aspxTo be usable as a data file for bulk import, a CSV file must comply with the following restrictions: Data fields never contain the field terminator. Either none or all of the values in a data field are enclosed in quotation marks ("").Now, "logic around bulk insert to fail bulk insert if csv file contains bad data" depends on what you mean.e.g. if the CSV file violates the restrictions above, BULK INSERT may not fail but still yield incorrect results. assume you had just two fields in your CSV: Name, Address. A good record may look like:Uncle Bob, 1 Main St.A record may be invalid:Aunt SueCausing BULK INSERT to fail (no address field.)A record may look OK but contain bad data:Skywalker, LukeBULK INSERT would succeed but take Luke to be the address of Skywalker.So, there are many possible combinations where the operation will fail, and still more where it will succeed but yield garbage.If instead you wrote a SSIS package to do the import, you can do full validation, insert only good rows, and direct error rows to another file for further analysis. |
 |
|
|
Johhny Blay
Starting Member
3 Posts |
Posted - 2015-03-31 : 10:17:35
|
| Thanks gbrittonjb |
|
|
|
|
|
|
|
|
Logic around Bulk Insert