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, Jan 25 '13 at 16:57
duplicate http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database — Vahid Farahmand, Jan 25 '13 at 16:59
@scones: those dreaded backticks only work for MySQL (and should be avoided in the first place). — , Jan 25 '13 at 17:23

Deepak Bhatia · Answer 1 · 2013-10-29T12:30:10.940

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.

score 0 · Answer 2 · answered Jan 25 '13 at 16:59

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 '13 at 16:59

Hamlet Hakobyan

32,965
6
52
68

score 0 · Answer 3 · answered Jan 25 '13 at 17:00

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 '13 at 17:00

DARK_A

575
1
7
28

No,that doesn't work either.It says you can execute one statement at one time. – John Rock Jan 25 '13 at 17:02

score 0 · Answer 4 · answered Jan 25 '13 at 17:22

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 '13 at 17:22

Fathah Rehman P

8,401
4
40
42

Let me give you more details.These are the tables I have : http://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 :) – John Rock Jan 25 '13 at 17:31
@JohnRock - i tried to open that url but got message "You must be logged-in to view the content you chose." – Fathah Rehman P Jan 25 '13 at 17:35
@JohnRock - if you give remote access,i think i can solve this – Fathah Rehman P Jan 25 '13 at 17:40
@JohnRock - goto following url, you can see screenshot of what i've done http://www74.zippyshare.com/v/92634544/file.html – Fathah Rehman P Jan 25 '13 at 17:53
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. – John Rock Jan 25 '13 at 18:01
@JohnRock - run following query and please tell me the result ".schema Rating" (without quotes) – Fathah Rehman P Jan 25 '13 at 18:06
Fails to execute.I'm going nuts :S – John Rock Jan 25 '13 at 18:08
@JohnRock - may be you forgot that dot(.) before schema . Above query will show you create query of Rating table – Fathah Rehman P Jan 25 '13 at 18:09
No I didn't.Can you join Class2Go and see it?It's Stanford's course.Name is Int to Databases. – John Rock Jan 25 '13 at 18:11
How do I send you pm or something :) – John Rock Jan 25 '13 at 18:24
@ i cant understand what you said – Fathah Rehman P Jan 25 '13 at 18:25
Is there a way to send you a message,privately? – John Rock Jan 25 '13 at 18:27
Man I'm sorry.Skype is not working :( But good news is I solved the question.Here it is : insert into Rating (rID, mID, stars, ratingDate) 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..... I'll add you as soon as it works :) – John Rock Jan 25 '13 at 18:53
@JohnRock - Good. By the way the reason for skype not working may be the antivirus may be blocking skype. Try to disable antivirus and login in skype. Anyway Have a nice day .. Take care . bye – Fathah Rehman P Jan 25 '13 at 18:57

Adding multiple rows in SQL

4 Answers4