Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

datatype larger than 255? Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 9:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:57 PM
Points: 33,206, Visits: 15,361
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
Posted Wednesday, January 16, 2013 9:20 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Wednesday, January 16, 2013 9:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
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
Posted Wednesday, January 16, 2013 9:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1407937
Posted Wednesday, January 16, 2013 9:36 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Wednesday, January 16, 2013 9:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
Both yours and the post above yours were most helpful and looks like I'm on the right track finally... thanks!
Post #1407948
Posted Wednesday, January 16, 2013 9:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1407957
Posted Wednesday, January 16, 2013 12:55 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 4,238, Visits: 3,258
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
Post #1408046
Posted Wednesday, January 16, 2013 6:38 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Thursday, January 17, 2013 12:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:05 AM
Points: 122, Visits: 292
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 »

Add to briefcase ««12

Permissions Expand / Collapse