[sqlite] Limit on the Compound Select Statements
Abhinav Upadhyay
I have a single column table, in which I wish to store around several
thousands of rows. I was wondering if I could insert them using a
single INSERT query and came across this Stackoverflow answer:
http://stackoverflow.com/a/1734067/348637 . According to that answer
it is possible to insert multiple rows using a single query with an
INSERT statement of the following form:
INSERT INTO table_name
SELECT 'val1' as col_name
UNION SELECT 'val2'
UNION SELECT 'val3'...
This seems to work but in my case I get an error sometimes saying "Too
many terms in the compound select statement" , I do not remember the
exact error message but it close to this. As per the documentation on
the compound select statements
(http://www.sqlite.org/lang_select.html) on Sqlite website, there is
no mention of an explicit limit. I would like to know the exact limit
on this, so that I could my code to work within this limit :)
Thanks
Abhinav
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Simon Slavin
On 23 Feb 2012, at 1:16pm, Abhinav Upadhyay <er.abhina...@gmail.com> wrote:
> I do not remember the
> exact error message but it close to this. As per the documentation on
> the compound select statements
> (http://www.sqlite.org/lang_select.html) on Sqlite website, there is
> no mention of an explicit limit. I would like to know the exact limit
> on this, so that I could my code to work within this limit
<http://www.sqlite.org/limits.html>
especially item 3, but also others.
However, I question the advantage of doing one long INSERT rather than doing many inside a transaction. Are you binding parameters ?
Simon.
Petite Abeille
On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote:
> I would like to know the exact limit
> on this, so that I could my code to work within this limit :)
See Maximum Number Of Terms In A Compound SELECT Statement:
http://www.sqlite.org/limits.html
Petite Abeille
On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote:
> . I was wondering if I could insert them using a
> single INSERT query
Ah, also, there is not much benefit in using a compound insert.
You could as well simply insert all your values in one transaction and be done.
On the other hand, the forthcoming 3.7.11 release seems to support multi-valued insert statements.
http://www.sqlite.org/draft/releaselog/3_7_11.html
Richard Hipp
>
> On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote:
>
> > . I was wondering if I could insert them using a
> > single INSERT query
>
> Ah, also, there is not much benefit in using a compound insert.
>
> You could as well simply insert all your values in one transaction and be
> done.
>
> On the other hand, the forthcoming 3.7.11 release seems to support
> multi-valued insert statements.
>
> http://www.sqlite.org/draft/releaselog/3_7_11.html
>
The new multi-valued insert is merely syntactic suger for the compound
insert. There is no performance advantage one way or the other.
>
> _______________________________________________
> sqlite-users mailing list
> sqlite...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
Abhinav Upadhyay
>
> On 23 Feb 2012, at 1:16pm, Abhinav Upadhyay <er.abhina...@gmail.com> wrote:
>
>> I do not remember the
>> exact error message but it close to this. As per the documentation on
>> the compound select statements
>> (http://www.sqlite.org/lang_select.html) on Sqlite website, there is
>> no mention of an explicit limit. I would like to know the exact limit
>> on this, so that I could my code to work within this limit
>
> <http://www.sqlite.org/limits.html>
>
> especially item 3, but also others.
>
> However, I question the advantage of doing one long INSERT rather than doing many inside a transaction. Are you binding parameters ?
>
It was already inside a bigger transaction, I was trying out something
naive and turns out it is not worth it. Thanks for the pointer :)
--
Abhinav
Don V Nielsen
text file? It separates the data from the application, and simplifies
making future changes to the list.