String or binary data would be truncated
Tonight I've been working on migrating some data from a CSV file to a MS SQL database. This is quite easy to do. Simply copy the CSV file into the database and then create a query to copy the data over... errr... okay, not so easy then when you get this error:
String or binary data would be truncated
The main problem is that you don't know which filed will be truncated. The way I managed to solve this was
to add this line to the top of my copy query:
SET ANSI_WARNINGS OFF
So my query was:
SET ANSI_WARNINGS OFF
INSERT INTO tblVendorProduct_141
(productCategoryID,
productCode,
productName,
deliveryDisclaimer,
longDescription,
tradePrice,
weight_kg,
dimensions,
imagePathThumb,
imagePath,
extraText,
vendorProductID,
RRP,
vendorID)
SELECT tblProductGroupCategory.productGroupCategoryID AS productCategoryID,
import_28Feb07.[Product ID] AS productCode,
import_28Feb07.productName,
import_28Feb07.aText AS deliveryDisclaimer,
import_28Feb07.productDescription AS longDescription,
import_28Feb07.priceExVat AS tradePrice,
import_28Feb07.Weight AS weight_kg,
import_28Feb07.Dimensions,
import_28Feb07.imageThumb AS imagePathThumb,
import_28Feb07.imageFull AS imagePath,
import_28Feb07.productDetails AS extraText ,
import_28Feb07.JGID AS vendorProductID,
import_28Feb07.Price AS RRP,
'141'
FROM import_28Feb07 INNER JOIN
tblProductGroupCategory ON import_28Feb07.Category = tblProductGroupCategory.productGroupCategoryName
INSERT INTO tblVendorProduct_141
(productCategoryID,
productCode,
productName,
deliveryDisclaimer,
longDescription,
tradePrice,
weight_kg,
dimensions,
imagePathThumb,
imagePath,
extraText,
vendorProductID,
RRP,
vendorID)
SELECT tblProductGroupCategory.productGroupCategoryID AS productCategoryID,
import_28Feb07.[Product ID] AS productCode,
import_28Feb07.productName,
import_28Feb07.aText AS deliveryDisclaimer,
import_28Feb07.productDescription AS longDescription,
import_28Feb07.priceExVat AS tradePrice,
import_28Feb07.Weight AS weight_kg,
import_28Feb07.Dimensions,
import_28Feb07.imageThumb AS imagePathThumb,
import_28Feb07.imageFull AS imagePath,
import_28Feb07.productDetails AS extraText ,
import_28Feb07.JGID AS vendorProductID,
import_28Feb07.Price AS RRP,
'141'
FROM import_28Feb07 INNER JOIN
tblProductGroupCategory ON import_28Feb07.Category = tblProductGroupCategory.productGroupCategoryName
Two caveats take from http://www.thescripts.com/forum/thread79256.html:
o ANSI_WARNINGS must be on for queries that involves linked servers, indexed views and indexes on computed columns. Thus setting ANSI_WARNINGS OFF can cause the query to fail to run much slower.
o Putting SET ANSI_WARNINGS OFF in a procedure will trigger a recompilation of the procedure, so it might be better to set it before calling the procedure.
This will allow the data to be imported without generating any errors - but surely the field(s) which are too large will still be truncated.
Normally it is relatively easy to see which field(s) may be causing the problem by comparing the data-types of the temporary import table, with those of the destination table. The problem is almost certainly in a (n)varchar / (n)text field which is going to a shorter (n)varchar field - which will probably narrow down the possible problem fields to 1 or 2.
As long as the source fields are (n)varchar rather than text, then you should be able to get the lengths of the data contained therein using the SQL len() function on the source fields - and comparing that with the size of the destination field
e.g select len(source.problemfield1) as problemfield1_lenght, len(sourceproblemfield2) as problemfield2_length
from source
where len(source.problemfield1) > 100 or len(sourceproblemfield2) >50
(assuming destinationfield1 is a varchar(100) and destinationfield2 is a varchar(50)
I agree with what you are saying. Sometimes though you have some rather large queries and by setting ANSI warnings off, this allows you to get the data into the table. Once the data is in the table you can review it and spot which filed is having the issue. In the case above I noticed from doing this that I hadn't set a set a long enough value on a varchar i.e
[fieldname] [varchar] (50) NULL
I had
[fieldname] [varchar] NULL.
I always prefer to plan my data transfers, and compare the data structures etc first, rather than do it with errors hidden, and then find the errors...
That's probably because I am a Planner (http://danlance.co.uk/index.cfm/2007/3/6/My-Progra...) - and you are a Doer (http://www.succor.co.uk/index.cfm/2007/3/4/My-prog...) ;)