<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
datatype larger than 255?
20 posts, Page 2 of 2
««
1
2
datatype larger than 255?
Rate Topic
Display Mode
Topic Options
Author
Message
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Wednesday, January 16, 2013 9:13 AM
SSC-Dedicated
Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406,
Visits: 13,722
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
Post #1407912
GSquared
GSquared
Posted Wednesday, January 16, 2013 9:20 AM
SSCoach
Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439,
Visits: 9,569
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
Post #1407917
briancampbellmcad
briancampbellmcad
Posted Wednesday, January 16, 2013 9:28 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 13, 2013 12:09 PM
Points: 101,
Visits: 246
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.
Post #1407931
Sean Lange
Sean Lange
Posted Wednesday, January 16, 2013 9:35 AM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547,
Visits: 8,204
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 Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1407937
GSquared
GSquared
Posted Wednesday, January 16, 2013 9:36 AM
SSCoach
Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439,
Visits: 9,569
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
Post #1407938
briancampbellmcad
briancampbellmcad
Posted Wednesday, January 16, 2013 9:47 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 13, 2013 12:09 PM
Points: 101,
Visits: 246
Both yours and the post above yours were most helpful and looks like I'm on the right track finally... thanks!
Post #1407948
Sean Lange
Sean Lange
Posted Wednesday, January 16, 2013 9:54 AM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547,
Visits: 8,204
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 Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1407957
Ed Wagner
Ed Wagner
Posted Wednesday, January 16, 2013 12:55 PM
SSCommitted
Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,856,
Visits: 528
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...
Post #1408046
GSquared
GSquared
Posted Wednesday, January 16, 2013 6:38 PM
SSCoach
Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439,
Visits: 9,569
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
Post #1408119
SQL_By_Chance
SQL_By_Chance
Posted Thursday, January 17, 2013 12:53 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, April 01, 2013 1:11 PM
Points: 104,
Visits: 247
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.”
Post #1408190
« Prev Topic
|
Next Topic »
20 posts, Page 2 of 2
««
1
2
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
