SQL Server doesn't allow Insert of list of multiple values

Question

I tried this query in SQL Server:

INSERT INTO Buchungsquelle (buchungsquelle) 
VALUES (34776), (35025), (35027), (35036), (35079), (35080), (35081),(35082), (35101);

however, I get an error:

Wrong syntax near ','

What am I doing wrong?

EDIT: table creation statement:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Buchungsquelle]
(
    [buchungsquelle] [int] IDENTITY(1,1) NOT NULL,
    PRIMARY KEY CLUSTERED ([buchungsquelle] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

EDIT: I found the error now, I have a SQL Server 2005 Version installed on testsystem here ;) So I guess, it doesn't work with that syntax anyway... is there some other way to do it easier?

What is the data type of `buchungsquelle` ? It is working fine for me. — Arulkumar, Apr 26 '19 at 13:02
I [can't replicate](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=fd9f81d1d9857236562ae63b4cc1f545) your problem. — Thom A, Apr 26 '19 at 13:04
Have a look at this [answer](https://stackoverflow.com/a/19763826/2451726). Can you copy, paste the content into a notepad to see any unprintable characters are there. — Arulkumar, Apr 26 '19 at 13:06
That code still [doesn't replicate](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=dffc91dedf0b24b935ea31eeca4b48cc) the issue but generates a different error. Why are you trying to insert into a column with the `IDENTITY` property? — Thom A, Apr 26 '19 at 13:16
I didn't want that IDENTITY property, how can I switch this off? — MMMM, Apr 26 '19 at 13:25
What version of SQL are you using? Multiple inserts will NOTwork in older versions of sql e.g. SQL 2000. — Salman A, Apr 26 '19 at 13:27
Possible duplicate of https://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part — Salman A, Apr 26 '19 at 13:29
You can't "switch it off" @user2774480. You would have to `DROP` the column and `ALTER` your table and `ADD` a new column (without the `IDENTITY` property). — Thom A, Apr 26 '19 at 13:34
ok I did that, I have SQL Server 2017 version but it still doesnt work. — MMMM, Apr 26 '19 at 14:16
Can you show your updated `Create Table` statement. I cannot replicate your error. — SS_DBA, Apr 26 '19 at 14:29
I just found out I have SQL Server 2005... so doesn't work anyways — MMMM, Apr 29 '19 at 13:57

score 0 · Answer 1 · answered May 31 '19 at 05:21

As the table structure says, your column is Auto incremented, so first set identity insert on for your table. After that only you can insert values in identity set column.

For your concern please try this...

    set identity_insert Buchungsquelle on  
    Go

    INSERT INTO Buchungsquelle (buchungsquelle) 
    VALUES (34776), (35025), (35027), (35036), (35079), (35080), (35081),(35082), (35101);

    Go

    set identity_insert Buchungsquelle off  
    Go

SQL Server doesn't allow Insert of list of multiple values

1 Answers1