Adding multiple rows in SQL

Question

I am asked to add 8 rows into a table.

insert into Rating ( rID, mID, stars, ratingDate ) 
values ('207', '101', '5', null), ('207', '102', '5', null),
       ('207', '103', '5', null), ('207', '104', '5', null),
       ('207', '105', '5', null), ('207', '106', '5', null),
       ('207', '107', '5', null), ('207', '108', '5', null)

This operation works good with one value added but when adding multiple gives the error

Query failed to execute: near ",": syntax error

What is missing?

SQL is just the Structured Query Language - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what database system (and which version) you're using.... — marc_s
– marc_s, Commented Jan 25, 2013 at 16:57

Deepak Bhatia · Accepted Answer · 2013-10-29 12:30:10Z

A late answer If your are using SQLITE version 3.7.11 or above, then multiple rows insert is possible by this syntax,

SIMPLEST WAY

INSERT INTO Rating (rID, mID, stars, ratingDate) VALUES ('207', '102', '5', null) , ('207', '102', '5', null) , ('207', '102', '5', null)

The above clause posted in question do work if the new SQLITE version is used.

SELECT CLAUSE

insert into Rating 
        SELECT '207' AS rID, '101' AS mID, '5' AS stars, null AS ratingDate   
  UNION SELECT '207', '102', '5', null
  UNION SELECT '207', '103', '5', null
  UNION SELECT '207', '104', '5', null
  UNION SELECT '207', '105', '5', null
  UNION SELECT '207', '106', '5', null            
  UNION SELECT '207', '107', '5', null
  UNION SELECT '207', '108', '5', null

or SQL is

insert into Rating (rID, mID, stars, ratingDate)
        SELECT '207', '101', '5', null 
  UNION SELECT '207', '102', '5', null
  UNION SELECT '207', '103', '5', null
  UNION SELECT '207', '104', '5', null
  UNION SELECT '207', '105', '5', null
  UNION SELECT '207', '106', '5', null            
  UNION SELECT '207', '107', '5', null
  UNION SELECT '207', '108', '5', null

REMEMBER I you do not want to check for duplicate in above set of inserted values then use UNION ALL in place of UNION as it will be little faster.

Hamlet Hakobyan · Accepted Answer · 2013-01-25 16:59:49Z

0

I assume your RDBMS don't support such construction.

insert into Rating ( rID, mID, stars, ratingDate ) 
values ('207', '101', '5', null);
insert into Rating ( rID, mID, stars, ratingDate ) 
values ('207', '102', '5', null);
.....

answered Jan 25, 2013 at 16:59

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Comments

DARK_A · Accepted Answer · 2013-01-25 17:00:09Z

0

I sugest:

insert into Rating ( rID, mID, stars, ratingDate ) values ('207', '101', '5', null);
insert into Rating ( rID, mID, stars, ratingDate ) values ('207', '102', '5', null);
...
insert into Rating ( rID, mID, stars, ratingDate ) values ('207', '108', '5', null);

answered Jan 25, 2013 at 17:00

DARK_A

5751 gold badge7 silver badges29 bronze badges

1 Comment

John Rock Over a year ago

No,that doesn't work either.It says you can execute one statement at one time.

Fathah Rehman P · Accepted Answer · 2013-01-25 17:22:19Z

0

i created table in sql lite . table creation script is as follows

 create table Rating (rID varchar(10),mID varchar(10),stars varchar(10),ratingDate date);

And i used following query to insert into above table and its working fine for me.

insert into Rating ( rID, mID, stars, ratingDate ) 
values ('207', '101', '5', null), ('207', '102', '5', null),
       ('207', '103', '5', null), ('207', '104', '5', null),
       ('207', '105', '5', null), ('207', '106', '5', null),
       ('207', '107', '5', null), ('207', '108', '5', null);

answered Jan 25, 2013 at 17:22

Fathah Rehman P

8,8014 gold badges43 silver badges42 bronze badges

14 Comments

John Rock Over a year ago

Let me give you more details.These are the tables I have : class2go.stanford.edu/db/Winter2013/pages/moviedata I am asked to "Insert 5-star ratings by James Cameron for all movies in the database. Leave the review date as NULL." Hope that helps :)

Fathah Rehman P Over a year ago

@JohnRock - i tried to open that url but got message "You must be logged-in to view the content you chose."

Fathah Rehman P Over a year ago

@JohnRock - if you give remote access,i think i can solve this

Fathah Rehman P Over a year ago

@JohnRock - goto following url, you can see screenshot of what i've done www74.zippyshare.com/v/92634544/file.html

John Rock Over a year ago

If you create the table it works but it is already there.So it doesn't work.My understanding is I have to write a query that inserts these values not inserting them one by one.

|

Collectives™ on Stack Overflow

Adding multiple rows in SQL

4 Answers 4

Comments

Comments

1 Comment

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related