January 16, 2013 at 7:57 am
I have to import some data that has text fields that can run from 300-400 characters... is there a datatype I can change my textfield to that will allow for that?
January 16, 2013 at 8:07 am
Normal varchar can go up to 8000 characters if you define it that way. Varchar(max) can go up to 2 Gig (approximately 2 billion characters).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2013 at 8:07 am
varchar(400), nvarchar(400) depending on if it is unicode or non-unicode data.
January 16, 2013 at 8:10 am
You can use varchar(XXXX) or char(XXXX). Instead of XXXX you can specify a number up to 8000. The number represents the number of characters that you'll have. You can also use varchar(max) or char(max) to work with much bigger strings. I suggest that you'll read a bit about SQL Server data types - http://msdn.microsoft.com/en-us/library/ms187752.aspx
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 16, 2013 at 8:28 am
The reason I was asking was I was loading about 20,000 records and kept getting an error popup "String or binary data would be truncated".... looked through the data and found no fields actually that exceeded the 255. So still searching....
January 16, 2013 at 8:36 am
Are you using the Import Wizard in Management Studio? Or an SSIS package? Or something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2013 at 8:44 am
When you looked through the data, do you mean in SSMS? Or did you query something like LEN()?
January 16, 2013 at 8:45 am
Actually I'm using SQL Server Express so I'm pasting them from copying the data from a .csv file. Is there another way with SQL Server Express?
January 16, 2013 at 8:55 am
briancampbellmcad (1/16/2013)
Actually I'm using SQL Server Express so I'm pasting them from copying the data from a .csv file. Is there another way with SQL Server Express?
Yes as Steve suggested use the LEN function.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 16, 2013 at 9:07 am
After I pasted the errors began. I looked back at my data in my .csv and found no reason that all the records should not commit. Not sure how to use the LEN function in this case although I'm familiar with it "SELECT LEN(Address) as LengthOfAddress FROM Persons". I'd would be looking at the data that commited rather than the one's that failed (?). I also have access only to SSMS in SQL Server Express so my options are limited.
January 16, 2013 at 9:13 am
If you have the files in a CSV, then you can put a column at the right and use a formula for =LEN(A1) or whatever the column in.
To import into SQL Server, you can use BCP.
http://msdn.microsoft.com/en-us/library/aa174646%28v=SQL.80%29.aspx
However this error is what it says it is. You are inserting more data into a field than the field has space for.
January 16, 2013 at 9:20 am
When you say you're copy-pasting from CSV, do you mean you're opening a CSV file in Excel, copying the contents, then pasting directly into a table via "Edit top 200 rows" in SSMS?
I'm asking because, depending on how you do that, it can end up trying to copy the whole block of text into the first column, first row, and you can definitely get a truncation error from that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2013 at 9:28 am
Yes using "opening a CSV file in Excel, copying the contents, then pasting directly into a table via "Edit top 200 rows" in SSMS"... which works well for about 19,500 out of 20,400 rows... I can find nothing unique or special about the rows that fail... always the same rows on repeat attempts... scanned the .csv visually and counted the longest fields and found none that exceeded the limit of my SQL Server fields.
January 16, 2013 at 9:35 am
briancampbellmcad (1/16/2013)
Yes using "opening a CSV file in Excel, copying the contents, then pasting directly into a table via "Edit top 200 rows" in SSMS"... which works well for about 19,500 out of 20,400 rows... I can find nothing unique or special about the rows that fail... always the same rows on repeat attempts... scanned the .csv visually and counted the longest fields and found none that exceeded the limit of my SQL Server fields.
As Gus said that will cause issues. Honestly you would be far better using excel to generate insert scripts instead of using the edit top 200 rows "feature". I have heard nothing but horror stories of that.
To leverage Excel to generate your data you can add a new column and use the CONCATENATE function in excel.
Say your data is in columns A - D you can add in column E (you may have to fiddle with the ' to suit your datatypes)
=CONCATENATE("Select '", Col1, "', '", Col2, "', '", Col3, "', '", Col4, "' union all")
Then use the copy cell feature to copy that function down the list. Copy the entire column and paste it into a query window.
At the top add "Insert MyTable (Col1, Col2, Col3, Col4)"
Then go to the last row and remove the union all.
Voila! In 30 seconds you created a massive insert statement that will work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 16, 2013 at 9:36 am
You should be able to save yourself a significant amount of work by using the Import Wizard. Right-click the database in the object explorer in SSMS, pick Tasks -> Import Data, and follow the directions. When you're defining the data source (the one you want is Flat File and you might have to change the file extension in the Explorer window it opens so that it will show csv instead of just txt), you may need to use the Advanced tab to set the column widths wider than 50.
Try that, see if it helps.
It should allow you to import a whole CSV in about 1 minute, less when you get used to using it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply