importing a text file

Hi
I am trying to import a text file into sql server..but i am not able to do so..because the size of the var type is 8000..
and i have too many columns to change the variable manually
thus i copied the create table query from the import window
and i got an error
The table 'test' has been created but its maximum row size (25863) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

can some help
thanksDid you consider to use the memo data type TEXT instead VARCHAR?|||Is you column defined as char or is it defined as varchar?

A char column always requires the same amount of space, equivalent to its defined length.

A varchar column only requires as much space as the data it holds.

Are you trying to import an entire record into a single field? If you have a single field in your source data that is 8000+ characters, your only option is to split it or use a text or image datatype.

If you are still having problems, post your table definition and the sql you using to import the data.

blindman|||I don't think SQL would even allow you to create a table of the specified size with CHAR. Have you tried it yourself, bm?:cool:|||Thank you for all the response..
the solution to my problem..I edit the create table query during my import process and change the 8000 to 255 and then with that edited query i make an empty table and then i append my orginal text file with data into that empty table..
Well it is a long process..but it keeps going.
thanks for the help|||Are you saying that a maximum size of 255 is sufficient for you? If not, I repeat myself, consider to use TEXT instead of VARCHAR. It allows you to import texts of variable length without getting on the row length limits.|||The text and image type should not be used unless absolutely necessary. They require additional overhead, and have greatly limited functionality compared to char and varchar.

blindman|||I'd say, it's a pretty blunt statement. It invalidates the very presence of existence of companies that specialize in DMS (document management systems).|||Blunt yes, but not completely dull. Document Management is an area where the use of text and image data may be absolutely necessary.

blindman|||Document Management capabilities are present in many software products. Actually, those that are least reliable are the ones that avoid the usage of datatypes that are targeting this specific need, - to store large volumes of data. I've seen tricks like storing paths to actual document locations, or parsing text documents into 70-character lines. Cute, but if path is stored, - there is no guarantee for integrity of the referenced document, and in case of parsing - very resource-intensive in respect to drastic growth of such tables while loosing any formating information. Contrary to that, here we even store web pages in text fields and scan paper claims and store them into image. You also mentioned limited functionality? What do you mean? Each datatype should be treated accordingly, so if you attempt to treat text field using char/varchar approach, - sure, you loose functionality. But not because of "limited functionality" of the datatype... Wonder, because of what? ;)|||rdjabarov,

All the examples you mentioned are legitimate uses of text and image data.

As far as limited functionality, try indexing your text fields.
...or sorting them
...or using the LIKE operator
...or concatenating them

blindman|||bm: operations you mentioned are not designed for TEXT and IMAGE datatypes. But I like your way of looking at it:

- can you multiply a CHAR field?
- can you index a BIT field?
- can you store 2G of data into VARCHAR field?

Good luck answering :rolleyes:|||rdjabarov,

No. I use the appropriate datatype for the data. I recommend that you do the same. If you are using text and image columns in your design when you don't need to, then I pity the poor DBA who has to clean up after you when you get canned.

blindman|||By the way: what about a text-indexed table, or the powerful CONTAINS() function searching for words in a text field ?!

However, we shouldn't bother NosWal anymore. If anybody still has to say about the (ab)use of data types, I suggest to open a new thread.|||bm: you're REALLY on a mission. You just never answered what its name. What are you trying to say? It looks like you adore listening to yourself. I suggest call-forwarding services, until your office phone gets reassigned to your replacement. Then, - it'll be just this forum, until your internet connection gets cut due to late payments. Anything else you want to say please take it offline.|||rjabberon,

That makes five posts for you on this thread, and none of them have provided any assistance to noswal. Whatever mission I'm on, it ain't yours.|||that's a cute way of spelling my nick :)

But please don't count my posts, because your employer will fire you for your "math" :)