SQL 2005 update query issues
I'm trying to update a field in the database based off of an excel file that I have. I don't know SQL in the slightest but was tasked with this anyhow. Here's what I have:
use VETEST
update O
set O.USER_10 = E.UPC
from dbo.Part as O inner join
(
Select Part, UPC FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Parts.xls',
'SELECT Part, UPC FROM [sheet1$]')
)
as E on O.ID = E.Part
The error I am getting is:
Server: Msg 8152, Level 16, State 9, Procedure DETECT_EVENT, Line 12
String or binary data would be truncated.
The statement has been terminated.
Now the data I'm trying to insert is only 11 characters and the table property is formatted for 80 so that shouldn't be a problem. None-the-less I've tried casting the data, tried truncating the data, but nothing seems to work. What am I missing here? I noticed that if I even try to update the property with even one character specifically specified completely bypassing the Excel file that it still will not work. Any SQL gurus out there that can help? Google has lots of non-applicable/non-working advice so far.
use VETEST
update O
set O.USER_10 = E.UPC
from dbo.Part as O inner join
(
Select Part, UPC FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Parts.xls',
'SELECT Part, UPC FROM [sheet1$]')
)
as E on O.ID = E.Part
The error I am getting is:
Server: Msg 8152, Level 16, State 9, Procedure DETECT_EVENT, Line 12
String or binary data would be truncated.
The statement has been terminated.
Now the data I'm trying to insert is only 11 characters and the table property is formatted for 80 so that shouldn't be a problem. None-the-less I've tried casting the data, tried truncating the data, but nothing seems to work. What am I missing here? I noticed that if I even try to update the property with even one character specifically specified completely bypassing the Excel file that it still will not work. Any SQL gurus out there that can help? Google has lots of non-applicable/non-working advice so far.
Jumping on the IT blogging band wagon -- http://www.jefferyland.com/
Comments
-
undomiel
Member Posts: 2,818
Figured it out on my own eventually. Actually more like a hack of a work around. Had to import everything into a temporary table specially created by the query and then copy everything over to the target table with ANSI_WARNINGS turned off. Ugly, but it works, and hopefully I will never have to touch SQL again. Though my boss will probably be delighted that I made it easy instead of entering everything by hand and will find lots more SQL work for me. Sigh, lose-lose situation.
Still better than her original plan of me entering everything in by hand. Hell no!Jumping on the IT blogging band wagon -- http://www.jefferyland.com/