34,837
社区成员




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
执行红色部分是没问题的,看到很多人的解决办法是说插入时指定列,但是现在已经指定列了,还是报这个错,麻烦大佬们帮忙看下问题在哪
把下面的复制过去直接执行呢?
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
insert into MT_InspectItem(col1,col2...)
select col1,col2...from table
用这个方式。insert into语句不指定列名的话,很大几率会报错,特别是插入的表中有计算列或者自增列的时候。计算列和自增列是普通情况下是不能直接插入的。