Microsoft sql server 2014 Error on insert

Question

When I insert into table with this code I have this error.

INSERT INTO Ridic values(
1, 'Franta' , 'Popkorn'  ,
2, 'Slavěna', 'Zíková'   ,
3, 'Havel'  , 'Bravenec' ,
4, 'Rudolf' , 'Stibor'   ,
5, 'Miloš'  , 'Vorlíček' ,
6, 'Agáta'  , 'Krobotová'
)

Column name or number of supplied values does not match table definition.

But when I try insert only one row everything is OK

INSERT INTO Ridic values(1, 'Franta' , 'Popkorn')

I use SQL server 2014 management studio. there is table:

create table Ridic
(
UserID int primary key,
Jmeno varchar(10),
Prijmeni varchar(10)
)

[SQL SERVER – Three Methods to Insert Multiple Rows into Single Table](http://blog.sqlauthority.com/2012/08/29/sql-server-three-methods-to-insert-multiple-rows-into-single-table-sql-in-sixty-seconds-024-video/) — bansi, May 31 '16 at 16:42
Also your column data types are `Varchar` you have some unicode characters in your data like `ě` , `á` and `š` etc. You need to do two things, 1. change the data type from varchar to `NVARCHAR` . 2. When inserting data into columns prefix your string with `N` like .... `values( 1, N'Franta' , N'Popkorn') ` — M.Ali, May 31 '16 at 16:49

score 1 · Accepted Answer · answered May 31 '16 at 16:43

Every set of parenthesis in a values clause refers to a single row. In other words, this statement attempts to insert a single row with 18 columns, which of course fails (the line breaks, like any whitespace are inconsequential). Instead, you should have six sets of parenthesis, each with three columns:

INSERT INTO Ridic VALUES
(1, 'Franta' , 'Popkorn'  ),
(2, 'Slavěna', 'Zíková'   ),
(3, 'Havel'  , 'Bravenec' ),
(4, 'Rudolf' , 'Stibor'   ),
(5, 'Miloš'  , 'Vorlíček' ),
(6, 'Agáta'  , 'Krobotová')

I would suggest using field names also `INSERT INTO Ridic (UserID, Jmeno, Prijmeni) VALUES .....` — bansi, May 31 '16 at 16:46

score 0 · Answer 2 · answered May 31 '16 at 16:41

0

Test the following statement.

INSERT INTO Ridic values(
1, 'Franta' , 'Popkorn'),
(2, 'Slavěna', 'Zíková'),
(3, 'Havel'  , 'Bravenec'),
(4, 'Rudolf' , 'Stibor'),
(5, 'Miloš'  , 'Vorlíček'),
(6, 'Agáta'  , 'Krobotová')

answered May 31 '16 at 16:41

Emil Rowland

538
5
25

score 0 · Answer 3 · answered May 31 '16 at 16:41

You forgot some brackets:

insert  into Ridic
values  (1, 'Franta', 'Popkorn')  ,
        (2, 'Slavěna', 'Zíková')  ,
        (3, 'Havel', 'Bravenec')  ,
        (4, 'Rudolf', 'Stibor')  ,
        (5, 'Miloš', 'Vorlíček')  ,
        (6, 'Agáta', 'Krobotová')

score 0 · Answer 4 · answered May 31 '16 at 16:43

Try this:

INSERT  INTO Ridic
VALUES  ( 1, 'Franta', 'Popkorn' )  ,
        ( 2, 'Slavěna', 'Zíková' )   ,
        ( 3, 'Havel', 'Bravenec' ) ,
        ( 4, 'Rudolf', 'Stibor' )  ,
        ( 5, 'Miloš', 'Vorlíček' ) ,
        ( 6, 'Agáta', 'Krobotová' );

Microsoft sql server 2014 Error on insert

4 Answers4