我包括了所有相关的内容,直到出现错误,但在案例语句代码之前一切正常并且运行良好;运行时出现错误“列名或提供的值的数量与表定义不匹配”,但我终生无法找到问题。谢谢!
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].
[Classifications]') AND TYPE IN (N'U'))
DROP TABLE Classifications
GO
CREATE TABLE Classifications
(
SegmentID nvarchar(10),
Waterbody_Type nvarchar(10),
Waterbody_Name nvarchar(100),
Basin nvarchar(50),
Segment_Miles int,
Water_Quality_Class nvarchar(100)
)
BULK INSERT Classifications FROM 'C:\Project\Classifications.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a'
);
GO
ALTER TABLE Classifications ADD Segment_Length nvarchar(100)
DELETE FROM Classifications
WHERE COALESCE (Segment_Miles, Segment_Length) IS NULL;
INSERT INTO Classifications
SELECT SegmentID,
Segment_Length =
CASE
WHEN Segment_Miles >= 0 and Segment_Miles <= 50 THEN 'Short'
WHEN Segment_Miles > 50 and Segment_Miles <= 100 THEN 'Long'
ELSE '?'
END
FROM Classifications
我认为您的意思是要进行更新,而不是最后插入。看起来您正在尝试根据 Segment Miles 填充 Segment_Length 字段,而不是添加仅包含 SegmentID 和 Segment_Length 的新行。如果是这样 - 类似:
UPDATE Classifications
SET Segment_Length = CASE
WHEN Segment_Miles >= 0 and Segment_Miles <= 50 THEN 'Short'
WHEN Segment_Miles > 50 and Segment_Miles <= 100 THEN 'Long'
ELSE '?'
END
您收到错误的原因是您试图将记录插入具有 7 个字段的表中,但您的 select 语句仅包含 2 个字段。如果您实际上是在尝试插入新记录,则需要更改 INSERT 行以指定要填充的两个字段:
INSERT INTO Classifications (SegmentID, Segment_Length)
...
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句