SQL Server批量插入 - “批量加载数据转换错误” [英] SQL Server bulk insert - "Bulk load data conversion error"
问题描述
bulk insert dbo.A
FROM 'd:\AData.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
数据库，我在审查可疑数据后遇到无法解释的错误：
Inserting bulk data into a database, I approached an unexplainable error after review of the suspect data:
Msg 4867，Level 16，State 1，Line 6
第1列第42列（FES）的批量加载数据转换错误（溢出）。
FES列 tinyint ，现在 smallint 。 d：\AData.csv 在第1行第42列中包含 2 。是否有任何其他来源类型错误？
Column FES was tinyint, now smallint. d:\AData.csv contains a 2 in row 1, column 42. Are there any other sources for this type of error?
--- APPEND ---
---APPEND---
我不知道如何正确发布这个。
I'm not sure how to properly post this.
创建表脚本
create table AcsBy320082010Hus(
serialnoId bigint,
RT char,
DIVISION tinyint,
PUMA int,
REGION tinyint,
ST tinyint,
ADJHSG int,
ADJINC int,
WGTP int,
NP tinyint,
TYPE tinyint,
ACR tinyint,
AGS tinyint,
BDS tinyint,
BLD tinyint,
BUS tinyint,
CONP smallint,
ELEP smallint,
FS smallint,
FULP int,
GASP int,
HFL tinyint,
INSP varchar(6),
KIT tinyint,
MHP int,
MRGI tinyint,
MRGP int,
MRGT tinyint,
MRGX tinyint,
PLM tinyint,
RMS tinyint,
RNTM tinyint,
RNTP int,
SMP int,
TEL tinyint,
TEN tinyint,
VACS tinyint,
VAL tinyint,
VEH tinyint,
WATP int,
YBL tinyint,
FES tinyint,
FINCP int,
FPARC tinyint,
GRNTP int,
GRPIP tinyint,
HHL tinyint,
HHT tinyint,
HINCP int,
HUGCL bit,
HUPAC tinyint,
HUPAOC tinyint,
HUPARC tinyint,
LNGI tinyint,
MV tinyint,
NOC tinyint,
NPF tinyint,
NPP bit,
NR bit,
NRC tinyint,
OCPIP tinyint,
PARTNER tinyint,
PSF bit,
R18 bit,
R60 tinyint,
R65 tinyint,
RESMODE tinyint,
SMOCP int,
SMX tinyint,
SRNT bit,
SVAL bit,
TAXP tinyint,
WIF tinyint,
WKEXREL tinyint,
WORKSTAT tinyint,
FACRP bit,
FAGSP bit,
FBDSP bit,
FBLDP bit,
FBUSP bit,
FCONP bit,
FELEP bit,
FFSP bit,
FFULP bit,
FGASP bit,
FHFLP bit,
FINSP bit,
FKITP bit,
FMHP bit,
FMRGIP bit,
FMRGP bit,
FMRGTP bit,
FMRGXP bit,
FMVYP bit,
FPLMP bit,
FRMSP bit,
FRNTMP bit,
FRNTP bit,
FSMP bit,
FSMXHP bit,
FSMXSP bit,
FTAXP bit,
FTELP bit,
FTENP bit,
FVACSP bit,
FVALP bit,
FVEHP bit,
FWATP bit,
FYBLP bit,
WGTP1 int,
WGTP2 int,
WGTP3 int,
WGTP4 int,
WGTP5 int,
WGTP6 int,
WGTP7 int,
WGTP8 int,
WGTP9 int,
WGTP10 int,
WGTP11 int,
WGTP12 int,
WGTP13 int,
WGTP14 int,
WGTP15 int,
WGTP16 int,
WGTP17 int,
WGTP18 int,
WGTP19 int,
WGTP20 int,
WGTP21 int,
WGTP22 int,
WGTP23 int,
WGTP24 int,
WGTP25 int,
WGTP26 int,
WGTP27 int,
WGTP28 int,
WGTP29 int,
WGTP30 int,
WGTP31 int,
WGTP32 int,
WGTP33 int,
WGTP34 int,
WGTP35 int,
WGTP36 int,
WGTP37 int,
WGTP38 int,
WGTP39 int,
WGTP40 int,
WGTP41 int,
WGTP42 int,
WGTP43 int,
WGTP44 int,
WGTP45 int,
WGTP46 int,
WGTP47 int,
WGTP48 int,
WGTP49 int,
WGTP50 int,
WGTP51 int,
WGTP52 int,
WGTP53 int,
WGTP54 int,
WGTP55 int,
WGTP56 int,
WGTP57 int,
WGTP58 int,
WGTP59 int,
WGTP60 int,
WGTP61 int,
WGTP62 int,
WGTP63 int,
WGTP64 int,
WGTP65 int,
WGTP66 int,
WGTP67 int,
WGTP68 int,
WGTP69 int,
WGTP70 int,
WGTP71 int,
WGTP72 int,
WGTP73 int,
WGTP74 int,
WGTP75 int,
WGTP76 int,
WGTP77 int,
WGTP78 int,
WGTP79 int,
WGTP80 int)
标题行
serialno，RT，DIVISION，PUMA，REGION，ST，ADJHSG，ADJINC，WGTP，NP，TYPE，ACR，AGS ，BATH，BDSP，BLD，BUS，CONP，ELEP，FS，FULP，GASP，HFL，INSP，MHP，MRGI，MRGP，MRGT，MRGX，REFR，RMSP，RNTM，RNTP，RWAT，SINK，SMP，STOV，TEL ，TEN，TOIL，VACS，VALP，VEH，WATP，YBL，FES，FINCP，FPARC，GRNTP，GRPIP，HHL，HHT，HINCP，HUGCL，HUPAC，HUPAOC，HUPARC，KIT，LNGI，MULTG，MV，NOC，NPF ，NPP，NR，NRC，OCPIP，PARTNER，PLM，PSF，R18，R60，R65，RESMODE，SMOCP，SMX，SRNT，SVAL，TAXP，WIF，WKEXREL，WORKSTAT，FACRP，FAGSP，FBATHP，FBDSP，FBLDP，FBUSP ，FCONP，FELEP，FFSP，FFULP，FGASP，FHFLP，FINSP，FKITP，FMHP，FMRGIP，FMRGP，FMRGTP，FMRGXP，FMVP，FPLMP，FREFRP，FRMSP，FRNTMP，FRNTP，FRWATP，FSINKP，FSMP，FSMXHP，FSMXSP，FSTOVP ，FTAXP，FTELP，FTENP，FTOILP，FVACSP，FVALP，FVEHP，FWATP，FYBLP，WGTP1，WGTP2，WGTP3，WGTP4，WGTP5，WGTP6，WGTP7，WGTP8，WGTP9，WGTP10，WGTP11，WGTP12，WGTP13，WGTP14，WGTP15，WGTP16 ，WGTP17，WGTP18，WGTP19，WGTP20，WGTP21，WGTP22，WGTP23，WGTP24，WGTP25，WGTP26，WGTP27，WGTP28，WGTP29，WGTP30，WGTP31，WGTP32，WGTP33，WGTP34，WGTP35，WGTP36，WGTP37，WGTP38，WGTP39，WGTP40，WGTP41 ，WGTP42，WGTP43，WGTP44，WGTP45，WGTP46，WGTP47，WGTP48，WGTP49，WGTP50，WGTP51，WGTP52，WGTP53，WGTP54，WGTP55，WGTP56，WGTP57，WGTP58，WGTP59，WGTP60，WGTP61，WGTP62，WGTP63，WGTP64，WGTP65，WGTP66 ，WGTP67，WGTP68，WGTP69，WGTP70，WGTP71，WGTP72，WGTP73，WGTP74，WGTP75，WGTP76，WGTP77，WGTP78，WGTP79，WGTP80
serialno,RT,DIVISION,PUMA,REGION,ST,ADJHSG,ADJINC,WGTP,NP,TYPE,ACR,AGS,BATH,BDSP,BLD,BUS,CONP,ELEP,FS,FULP,GASP,HFL,INSP,MHP,MRGI,MRGP,MRGT,MRGX,REFR,RMSP,RNTM,RNTP,RWAT,SINK,SMP,STOV,TEL,TEN,TOIL,VACS,VALP,VEH,WATP,YBL,FES,FINCP,FPARC,GRNTP,GRPIP,HHL,HHT,HINCP,HUGCL,HUPAC,HUPAOC,HUPARC,KIT,LNGI,MULTG,MV,NOC,NPF,NPP,NR,NRC,OCPIP,PARTNER,PLM,PSF,R18,R60,R65,RESMODE,SMOCP,SMX,SRNT,SVAL,TAXP,WIF,WKEXREL,WORKSTAT,FACRP,FAGSP,FBATHP,FBDSP,FBLDP,FBUSP,FCONP,FELEP,FFSP,FFULP,FGASP,FHFLP,FINSP,FKITP,FMHP,FMRGIP,FMRGP,FMRGTP,FMRGXP,FMVP,FPLMP,FREFRP,FRMSP,FRNTMP,FRNTP,FRWATP,FSINKP,FSMP,FSMXHP,FSMXSP,FSTOVP,FTAXP,FTELP,FTENP,FTOILP,FVACSP,FVALP,FVEHP,FWATP,FYBLP,WGTP1,WGTP2,WGTP3,WGTP4,WGTP5,WGTP6,WGTP7,WGTP8,WGTP9,WGTP10,WGTP11,WGTP12,WGTP13,WGTP14,WGTP15,WGTP16,WGTP17,WGTP18,WGTP19,WGTP20,WGTP21,WGTP22,WGTP23,WGTP24,WGTP25,WGTP26,WGTP27,WGTP28,WGTP29,WGTP30,WGTP31,WGTP32,WGTP33,WGTP34,WGTP35,WGTP36,WGTP37,WGTP38,WGTP39,WGTP40,WGTP41,WGTP42,WGTP43,WGTP44,WGTP45,WGTP46,WGTP47,WGTP48,WGTP49,WGTP50,WGTP51,WGTP52,WGTP53,WGTP54,WGTP55,WGTP56,WGTP57,WGTP58,WGTP59,WGTP60,WGTP61,WGTP62,WGTP63,WGTP64,WGTP65,WGTP66,WGTP67,WGTP68,WGTP69,WGTP70,WGTP71,WGTP72,WGTP73,WGTP74,WGTP75,WGTP76,WGTP77,WGTP78,WGTP79,WGTP80
第1行
2008000000023，H，6,01300,3,01,1012650,1031272,00043,04,1,1 ,, 1,02,02,2,0000,150,2,000,2,020,3,00150，，2,00980,1,1,1,03 ,, 1,1,1，1,1,1,1， ，0210000,2,0080,09,2,000058100,2 ,,, 4,1,000058100,0,3,3,3,1,1,1,3,02,04,0,0,02,024,0 ，1,0,1,0,0,1,01170,3,0,1,19,1,3,3,0,0,0,0,0,0,0,0,0,0,0 ，0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 ，0,0,0,0,00112,00045,00068,00017,00044,00091,00071,00032,00036,00042,00048,00041,00013,00031,00063,00013,00037,00017,00063,00098,00026 ，00036,00017,00065,00036,00020,00014,00035,00037,00046,00047,00048,00080,00034,00012,00061,00031,00069,00043,00060,00011,00050,00016,00057,00050,00013 ，00016,00068,00056,00039,00042,00049,00081,00041,00012,00092,00050,00055,00049,00016,00062,00041,00103,00012,00048,00060,00073,00052,00063,00038,00047 ，00040,00017,00040,00059,00020,00042,00010,00038,00018
2008000000023,H,6,01300,3,01,1012650,1031272,00043,04,1,1,,1,02,02,2,0000,150,2,0002,020,3,00150,,2,00980,1,1,1,03,,,1,1,,1,1,1,1,,0210000,2,0080,09,2,000058100,2,,,4,1,000058100,0,3,3,3,1,1,1,3,02,04,0,0,02,024,0,1,0,1,0,0,1,01170,3,0,1,19,1,3,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,00112,00045,00068,00017,00044,00091,00071,00032,00036,00042,00048,00041,00013,00031,00063,00013,00037,00017,00063,00098,00026,00036,00017,00065,00036,00020,00014,00035,00037,00046,00047,00048,00080,00034,00012,00061,00031,00069,00043,00060,00011,00050,00016,00057,00050,00013,00016,00068,00056,00039,00042,00049,00081,00041,00012,00092,00050,00055,00049,00016,00062,00041,00103,00012,00048,00060,00073,00052,00063,00038,00047,00040,00017,00040,00059,00020,00042,00010,00038,00018
推荐答案
问题是你使用的数据类型。 tinyint 是只能处理从0到255的整数数据。 0210000 不在此范围内。
The problem is the datatypes that you're using. tinyint is only capable of handling integer data from 0 through 255. 0210000 is not within that range.
一个临时表，你转储这个文件，并使用all（n）varchars创建它？这样，您可以保留领先的 0 ，这可能是必要的，因为您正在分析的数据类型。
May I suggest creating a staging table into which you dump this file and create that with all (n)varchars? That way you preserve leading 0s which may be necessary given the type of data you're analyzing.
从登台表中，您可以将清理操作运行到报告结构中，这将允许您使用更合适的数据类型，您可以使用SQL查询来识别数据。
From the staging table you can run cleansing operations into a "reporting" structure that will allow you to use more appropriate data types which you can discern with SQL queries to profile your data.
这篇关于SQL Server批量插入 - “批量加载数据转换错误”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
