列名或所提供值的数目与表定义不匹配。

[goodcaoyu]

begin tran
insert into  MT_InspectItem 
select
serialno,
FeeItemCode,
[entry name],
Scope,
SpecimenId,
SpecimenName,
OrgId,
OptEmployeeId,
OptEmployeeName,
OptTime,
SpellCode,
StrokeCode,
Memo,
Status,
IsDelete,
InspectItemUnitId,
InspectItemUnitName

from  ws  where [entry name] not in(select d.[entry name] from (select a.* from  ws  a, MT_InspectItem  b where  b.InspectItemName like CONCAT('%',a.[entry name],'%')) d group by d.[entry name] having COUNT([entry name])>1
) and [amount of money] is not null 
rollback tran

 

执行红色部分是没问题的，看到很多人的解决办法是说插入时指定列，但是现在已经指定列了，还是报这个错，麻烦大佬们帮忙看下问题在哪

[goodcaoyu]

@shinger126 可以了感谢大哥

• 举报

回复 赞

[goodcaoyu]

@shinger126 谢谢

• 举报

回复 赞

[shinger126]

insert into MT_InspectItem（col1,col2...)
select col1,col2...from table
用这个方式。insert into语句不指定列名的话，很大几率会报错，特别是插入的表中有计算列或者自增列的时候。计算列和自增列是普通情况下是不能直接插入的。

• 打赏
• 举报

回复 赞

[goodcaoyu]

@吉普赛的歌 可以了 感谢感谢

• 举报

回复 赞

[goodcaoyu]

@吉普赛的歌 谢谢 我试下

• 举报

回复 赞

[吉普赛的歌]

把下面的复制过去直接执行呢？

BEGIN TRAN
INSERT INTO MT_InspectItem(
       serialno,
       FeeItemCode,
       [entry name],
       Scope,
       SpecimenId,
       SpecimenName,
       OrgId,
       OptEmployeeId,
       OptEmployeeName,
       OptTime,
       SpellCode,
       StrokeCode,
       Memo,
       STATUS,
       IsDelete,
       InspectItemUnitId,
       InspectItemUnitName    
)
SELECT serialno,
       FeeItemCode,
       [entry name],
       Scope,
       SpecimenId,
       SpecimenName,
       OrgId,
       OptEmployeeId,
       OptEmployeeName,
       OptTime,
       SpellCode,
       StrokeCode,
       Memo,
       STATUS,
       IsDelete,
       InspectItemUnitId,
       InspectItemUnitName
FROM   ws
WHERE  ws.[entry name] NOT IN 
(
    SELECT d.[entry name]
    FROM   (
                SELECT a.* 
                FROM   ws a,
                        MT_InspectItem b
                WHERE  b.InspectItemName LIKE CONCAT('%', a.[entry name], '%')
            ) d
    GROUP BY
            d.[entry name]
    HAVING COUNT([entry name]) > 1
)
AND [amount of money] IS NOT NULL
ROLLBACK TRAN

• 打赏
• 举报

回复 赞