April 25, 2012 at 3:04 pm
I need to insert into a new table A from one very big old table B.
In table B, the length of all fields set as varchar(255) but in new table, the length of many fields are small, like varchar(10), (20)...as real data should be.
Once I run insert script, I always get error like:"String or binary data would be truncated".
Is it possible to fix it? Or the new table need to set length as old table?
April 25, 2012 at 3:06 pm
What do you mean 'is it possible to fix it'? Do you want to force the data to be inserted in spite of being truncated? I think the problem is pretty clear...
_________________________________
seth delconte
http://sqlkeys.com
April 25, 2012 at 3:10 pm
The problem is all records stored in old table are the length about 20 but set as 255.
April 25, 2012 at 3:12 pm
Yes, so do you want to force all data from table B to 20 char length? What exactly do you want?
_________________________________
seth delconte
http://sqlkeys.com
April 25, 2012 at 3:19 pm
Yes, I want to force all data from table B to 20 char length
April 25, 2012 at 3:19 pm
Try to find the real length of the data in the old table. You can so SELECT MAX(LEN(columnA)), MAX(LEN(columnb)), ... to find out what your new table needs to have in order to accomodate the data.
Jared
CE - Microsoft
April 25, 2012 at 3:22 pm
SQLKnowItAll (4/25/2012)
Try to find the real length of the data in the old table. You can so SELECT MAX(LEN(columnA)), MAX(LEN(columnb)), ... to find out what your new table needs to have in order to accomodate the data.
i would agree that it would fix the issue to change the columns in the new database to match the old data.
adonetok (4/25/2012)
Yes, I want to force all data from table B to 20 char length
if you want to truncate the table you can do SELECT SUBSTRING(ColA,1,20) and manually chop the data. i would go with sqlknowitall's solution of figuring out how long the columns should be. would hate to be the one to explain why data got chopped off and is now nonsense with out the extra characters.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 25, 2012 at 6:31 pm
I suggest a modification to Capn Hector's suggestion as follows:
SELECT RTRIM(SUBSTRING(ColA,1,20))
This way, strings less than 20 in length will be stored without blank padding.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply