SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


datatype larger than 255?


datatype larger than 255?

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148412 Visits: 19444
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58711 Visits: 9730
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
briancampbellmcad
briancampbellmcad
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 Visits: 438
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63574 Visits: 17966
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58711 Visits: 9730
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
briancampbellmcad
briancampbellmcad
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 Visits: 438
Both yours and the post above yours were most helpful and looks like I'm on the right track finally... thanks!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63574 Visits: 17966
You're welcome. Glad we can help. I actually thought about suggesting using the Import Wizard but Gus already posted it. Either one of them should help.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Ed Wagner
Ed Wagner
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50155 Visits: 10844
I'm glad to see you're not going to copy and paste from Excel. I've found that when doing this, data isn't pasted it as it should be, so I avoid this like the plague. Dates and numeric strings formatted with leading zeros are especially problematic. It'll eventually get you into trouble.

Also, using SSMS's data import is so much faster than just pasting into a table that I can't even tell you. What would take me 20 minutes of waiting for a paste to complete would take a few seconds when using the process formerly known as DTS. There's those darn transaction logs that get hit by lots of pasting...


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58711 Visits: 9730
Ed Wagner (1/16/2013)
I'm glad to see you're not going to copy and paste from Excel. I've found that when doing this, data isn't pasted it as it should be, so I avoid this like the plague. Dates and numeric strings formatted with leading zeros are especially problematic. It'll eventually get you into trouble.

Also, using SSMS's data import is so much faster than just pasting into a table that I can't even tell you. What would take me 20 minutes of waiting for a paste to complete would take a few seconds when using the process formerly known as DTS. There's those darn transaction logs that get hit by lots of pasting...


It's not just the log.

The "Edit top 200 rows" is a cursor. Check it out in Profiler or a trace/Extended Events.

- 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
SQL_By_Chance
SQL_By_Chance
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 299
If you are importing using DTS/Import Export wizard. you might want to Edit the data type and length of the source. Cause thats what might be causing the truncate.

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search