SQL插入文本时遇varchar转int类型转换错误的排查与解决

嘿，我懂你碰到的这个坑——单独跑SELECT的时候Action列能完美标记符合条件的记录，结果一执行INSERT就炸出类型转换错误，而且目标表的Action明明是Nvarchar(50)类型，这确实挺让人摸不着头脑的。

你一开始怀疑CASE语句的类型问题其实没问题，但这次的罪魁祸首是INSERT语句的字段顺序不匹配：你写的INSERT没有明确指定目标表的字段列表，而SELECT子句里的字段顺序和目标表的实际字段顺序没对齐，导致原本要插入Action列的字符串值，被插到了某个int类型的字段里，这才触发了varchar转int的转换失败。

解决方法其实很简单： #

• 给INSERT语句明确指定目标表的字段列表，把所有字段按目标表的顺序列出来，确保每个值都对应到正确的列；
• 调整SELECT子句的字段顺序，让它和上面指定的字段列表完全一一对应。

补充说明： #

你已经确认目标表的Action列是Nvarchar(50)，所以你的CASE语句返回的字符串类型是完全匹配的，根本不需要额外用CONVERT函数，只要把字段顺序对齐，问题就迎刃而解了。

附上你提供的原代码供参考：

Insert Into tblwb_tt_RFID_DO (ItemID, ReceiverID, DMFsenderpanellisttypeid, DMFReceiverPanellistTypeID, ClassofMailID, DMFCalendarDateofPosting, QualityofServiceDateofPosting, DateofReceipt, FirstReadDate, FirstReadTime, FirstReadSite, Lastreadday, Lastreaddate, Lastreadtime, LastReadSite, ReceivesOnSaturday, ReceiverPostcode, freq, DMFanalysisperiodid, EmailAddress, [Action]) 
select d.ItemID, d.ReceiverId, d.DMFsenderPanellistTypeID, d.DMFReceiverPanellistTypeID, d.ClassOfMailID, 
CAST (d.DMFCalendarDateOfPosting as date) as 'DMFCalendarDateofposting', 
CAST (d.QualityOfServiceDateOfPosting as date) as 'Qualityofservicedateofposting', 
CAST (d.DateOfReceipt as date) as 'Dateofreceipt', 
CAST (d.FirstReadDate as date) as 'FirstReadDate', 
CAST (d.FirstReadDate as time) as 'FirstReadTime', 
d.FirstReadSite, 
datename (dw, d.lastreaddate) as 'LastReadDay', 
CAST (d.LastReadDate as date) as 'Lastreaddate', 
CAST (d.LastReadDate as time) as 'Lastreadtime', 
d.LastReadSite, 
CASE WHEN d.DMFReceiverPanellistTypeId = 2 then 1 ELSE d.ReceivesOnSaturday END as 'ReceivesOnSaturday', 
a.PostCode as 'Receiver Postcode', 
CASE WHEN ReceiverId is not null then 1 END as 'freq', 
CASE WHEN Datename (dw, d.lastreaddate) = 'Saturday' AND DATEDIFF(dd,DateOfReceipt,LastReadDate) <=2 THEN 'RESOLVED SATURDAY' 
     ELSE CASE WHEN Datename (dw, d.lastreaddate) = 'Sunday' AND DATEDIFF(dd,DateOfReceipt,LastReadDate) <=1 THEN 'RESOLVED SUNDAY' 
          ELSE NULL END END AS 'Action', 
d.DMFanalysisperiodid, 
p.EmailAddress 
from rmlivecopy..tblrmrsnapshot d 
join rmlivecopy..tblPanellist p on p.PanellistId = D.ReceiverId 
join rmlivecopy..tblAddress A on A.panellistID = P.panellistID 
join rmlivecopy..tblItemPlanInfo c on c.ItemId = d.ItemId AND d.dmfstatusID = 6 
left join rmlivecopy..tblitemquery iq on d.itemid = iq.itemid 
where d.DMFSurveysId in (1, 2, 5) 
and iq.querycode = 'R1' 
and iq.queryuseractionid = 'C' 
and iq.queryuseractionid <> 'A' 
and d.firstreadsite is not NULL 
and d.lastreadsite is not NULL 
and LastReadSite LIKE '%B1%' 
and DATEDIFF(dd,DateOfReceipt,LastReadDate) != 0 
and datepart(hh,LastReadDate) > 6 
and datepart(hh,LastReadDate) < 18 
and d.DMFanalysisperiodid like ('18%')

内容的提问来源于stack exchange，提问作者bagleyboy