0

Have the below SQL. I am getting an error message saying 'There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement'

CREATE TABLE #CaseList (
ID          INT IDENTITY(1, 1) NOT NULL,
CaseNumber  BIGINT NOT NULL,
ErrorReason VARCHAR(250) NOT NULL
)

SELECT * FROM #CaseList

SET IDENTITY_INSERT #CaseList ON
INSERT INTO #CaseList (ID, CaseNumber, ErrorReason)
VALUES (
1, 191541724, '',
2, 191546004, '',
3, 191547177, '',
4, 191549409, '',
5, 191551441, ''
)
SET IDENTITY_INSERT #CaseList OFF

Not sure what I am missing here since the number of my values matches with my temp table definition .. Thx :)

Chewy
  • 85
  • 2
  • 4
  • 15
  • Is it possible you already have another table with IDENTITY_INSERT set to on? Only one table per session can have identity insert turned on. See "Remarks" section here: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15 – beeker Mar 22 '21 at 20:53
  • ok. dumb question. if it is a temp table over which you have complete control, why create the column as an identity if you know you're just going to insert into it? – Jeremy Mar 22 '21 at 21:00
  • having all the values, comma separated, in VALUES it is treating it as if you are trying to insert 15 columns. should be `VALUES(1, 191541724, ''),(2, 191546004, '')` each one wrapped in parentheses, separated by a comma – Tim Mylott Mar 22 '21 at 21:05

1 Answers1

0

You must split the INSERT command into separate rows.

CREATE TABLE #CaseList (
ID          INT IDENTITY(1, 1) NOT NULL,
CaseNumber  BIGINT NOT NULL,
ErrorReason VARCHAR(250) NOT NULL
)

SELECT * FROM #CaseList

SET IDENTITY_INSERT #CaseList ON
INSERT INTO #CaseList (ID, CaseNumber, ErrorReason) VALUES (1, 191541724, '')
INSERT INTO #CaseList (ID, CaseNumber, ErrorReason) VALUES (2, 191546004, '')
INSERT INTO #CaseList (ID, CaseNumber, ErrorReason) VALUES (3, 191547177, '')
INSERT INTO #CaseList (ID, CaseNumber, ErrorReason) VALUES (4, 191549409, '')
INSERT INTO #CaseList (ID, CaseNumber, ErrorReason) VALUES (5, 191551441, '')
SET IDENTITY_INSERT #CaseList OFF

or even shorter as per this answer: https://stackoverflow.com/a/452934/15458459

SET IDENTITY_INSERT #CaseList ON
INSERT INTO #CaseList (ID, CaseNumber, ErrorReason) VALUES 
(1, 191541724, ''),
(2, 191546004, ''),
(3, 191547177, ''),
(4, 191549409, ''),
(5, 191551441, '')
SET IDENTITY_INSERT #CaseList OFF
Filip
  • 94
  • 1
  • 8