CASE WHEN Statement error in SQL: Column name or number of supplied values does not match table defi-CodePudding

I included everything relevant up until the error, but everything has worked and run fine prior to the case statement code; upon running I get the error "Column name or number of supplied values does not match table definition", but I cannot for the life of me find the issue. Thanks!

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

CodePudding user response：

I think you mean to be doing an update, not an insert at the end. It looks like you're trying to populate the Segment_Length field based on the Segment Miles, not add new rows with only the SegmentID and Segment_Length. If so - something like:

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

The reason you are getting the error is you are trying to insert records into a table with 7 fields, but your select statement contains only 2 fields. If you are actually trying to insert new records, then you need to change the INSERT line to specify which two fields you are populating:

INSERT INTO Classifications (SegmentID, Segment_Length) 
...