How can I insert a datattable to SQL using sqlbulkcopy when a datatable field for type string has more than 10000 characters

Question

0.00/5 (No votes)

See more:

Hello fellow developers ! I'm facing an issue where i need to insert a Datatable of 1000000 rows (and there are many of these in CSV files) into SQL server. I'm using SqlBulkCopy for the job.
The issue I'm facing is that one of the fields in the DataTable object, that is of type string can contain more than 10000 characters. The SQL Server table has this field set as Nvarchar(MAX) to fits the needs.
Alas, when I try to insert it an exception occurs and the system informs me that the data would be truncated for the specific field and therefore the operation stops.
Whats weird is that the database field has no problem handling the size (since it's Nvarchar(MAX) ).
I've read somewhere in MSDN that of a string is over 4000 characters (although the database can handle it) you need to explicitly set the string object ????
Any thoughts on these please ?

If you require any further details regarding the issue pelase let me know.
Thank you in advance.

What I have tried:

- Mapping fields in SqlBulkCopy
- Setting attributes to the record class of which I use to create the datatable columns (.e.g. [StringLengthMax])
- I use the FileHelpers Library to read the CSV file into a DataTable.

The process works fine if the field in the Datatable has a length <= 4000 characters.

Posted 5-Dec-17 22:18pm

peskandritsa2

Updated 7-Dec-17 20:57pm

Add a Solution

2 solutions

Solution 1

If you need to use text fields greater than nvarchar(MAX) try using ntext or text[^]. You may also need to refer to Bulk Copying Text and Image Data[^]

Alternatively, consider that you might want to store that text as a physical file on the server and only the path to it on the database (this is a common technique used for images for example)

Posted 6-Dec-17 3:16am

CHill60

Comments

W∴ Balboos, GHB 6-Dec-17 9:47am

nvarchar(MAX) holds all the text data you could reasonably want! Per your own reference:

IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

The new methods save me (at least) the trouble of using SQL pointers to do any kind of modifications to these fields - just follow the rules and never pass through any other (8K char limited) type or the truncation will occur.

peskandritsa2 6-Dec-17 15:19pm

The Database column is already set as NVarchar(MAX) ...the problem is that the string will not get copied in the column and the program will throw an exception...
string type on the code, NVarchar(MAX) type on the database. Could you be a bit more specific please regarding the previous answers ?

CHill60 7-Dec-17 4:49am

I'm not sure that I can be more specific - or did you mean to reply to Balboos? I suggested changing the column type to ntext - but be aware that it will be deprecated in the future. My suggestion to just store a path to a text file still stands, and, in my opinion, is the best option for you.

peskandritsa2 7-Dec-17 8:42am

I meant to reply @ Balboos. From what I've found out nvarchar(MAX) sotres up to 4000 chars. In order to store more than that, it uses a different page on the sql server. (This came straight from a Microsoft MVP specializing in SQL server) Unfortunately it seems that when using SqlBulkCopy, this is not the case. I don't know how to overcome this.

CHill60 7-Dec-17 9:08am

In order to store more than that it would use a different type! Yes paging would be affected but nothing, not just SqlBulkCopy, can magically force 10,000 characters into a 4,000 character field. You will need to reconsider your database structure - I've already given you one suggestion

Richard Deeming 8-Dec-17 13:57pm

I think you've both misunderstood. :)

nvarchar(max) can hold just as much text as ntext - slightly under 2.15 billion characters.

The old ntext type always stored the text in a separate page. nvarchar(max) will try to store the data inline within the record, until the length of the text exceeds 4000 characters. Once you go over that limit, it will automatically move the text to a separate page.

There is no advantage in using ntext over nvarchar(max), and many disadvantages.

CHill60 8-Dec-17 17:33pm

Thanks for clearing that up :thumbsup:...I'm still learning after all these years :-)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

peskandritsa2 · Accepted Answer · 2017-12-07T20:57:00

I found a work around for it, It seems that SqlBulkCopy has a limitation there (I wish I'm wrong), regarding the field length. What I did was to gather all the lengthy records (after a for loop to check them) into a new DataTable and then, for each row in the DataTable, I've created an SQL query that was run against the database. Turned out that it worked like charm with a minimal impact on performance.