SQLite3, combining multiple INSERT statements?

Question

I'm sure there must be an easy answer to this but everything I've googled seems to over complicate things and now I'm not so sure.

So here's what I'd have to do to insert records into my table now

INSERT INTO CAT_BUD_TAB (_id, CAT_ITEM, IN_OUT) VALUES (null, 'Student Loan', 'out');
INSERT INTO CAT_BUD_TAB (_id, CAT_ITEM, IN_OUT) VALUES (null, 'Food', 'in');

I figure there must be a shorter way, say something like..

INSERT INTO CAT_BUD_TAB (_id, CAT_ITEM, IN_OUT) VALUES (null, 'Student Loan', 'out'),(null, 'Food', 'in');

I'm using SQLite as I'm developing for android, but I'm pretty sure it's SQLite3 which means INSERTS like above should be possible right? How do I do it?

This question has also been answered here: http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database — McStretch, Dec 08 '10 at 15:29
Yeah i was getting mixed messages and thought maybe things had changes since last year, thanks. — Holly, Dec 08 '10 at 15:35

score 3 · Answer 1 · edited Feb 12 '12 at 05:38

3

Try use statement as folowing :

INSERT INTO table(field1,field2) SELECT "value1","value2" UNION SELECT "value1","value2" ...

edited Feb 12 '12 at 05:38

Abimaran Kugathasan

26,826
11
67
101

answered Feb 12 '12 at 03:55

Slach

929
7
19

Cristian · Answer 2 · 2012-02-16T04:48:36.377

2

Here is the official reference:

http://www.sqlite.org/lang_insert.html

However, it seems to be possible:

insert into myTable (col1,col2) 
     select aValue as col1,anotherValue as col2 
     union select moreValue,evenMoreValue 
     union...

Also, consider using things like:

begin transaction;
insert into ...;
insert into ...;
etc.
commit;

edited Feb 16 '12 at 04:48

answered Dec 08 '10 at 15:28

Cristian

191,931
60
351
260

score 1 · Answer 3 · answered Dec 08 '10 at 15:28

1

I don´t know to combining multiple Inserts. I think this is not possible!

Use transactions and prepared statements, if you want to insert a long list in a very short time.

For what do you need it?

answered Dec 08 '10 at 15:28

kodex83

49
7

Shouldn't this be a comment rather than an answer if kodex83 doesn't know the answer to the OP's question? – McStretch Dec 08 '10 at 15:32

score 1 · Answer 4 · answered Dec 08 '10 at 15:30

1

The docs on the SQL syntax understood by SQLite seem to indicate that only one set of VALUES can be used with each INSERT statement.

http://www.sqlite.org/lang_insert.html

answered Dec 08 '10 at 15:30

Alison R.

3,994
25
32

score 0 · Accepted Answer · edited Feb 16 '12 at 09:16

0

simple way is store the value1 and value2 in array and use a for loop like

for(int i = 0;i< value1.length,i++)
{
INSERT INTO CAT_BUD_TAB (_id, CAT_ITEM, IN_OUT) VALUES (null, 'value1[i]', 'value2[i]');
}

edited Feb 16 '12 at 09:16

McDowell

102,869
29
193
261

answered Feb 16 '12 at 05:18

user1203673

1,015
7
13

SQLite3, combining multiple INSERT statements?

5 Answers5