INSERT syntax missing key SQL syntax

classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|

INSERT syntax missing key SQL syntax

Jim Michaels-2
INSERT is supposed to handle multiple rows for VALUES.
for example,
INSERT INTO table(digit,dialpadstr) VALUES
(2,'abc'),
(3,'def'),
(4,'ghi'),
(5,'jkl'),
(6,'mno'),
(7,'pqrs'),
(8,'tuv'),
(9,'wxyz');


currently, sqlite only handles 1 row.
INSERT INTO table(digit,dialpadstr) VALUES
(2,'abc');

--
Jim Michaels
[hidden email]
http://JimsComputerRepairandWebDesign.com
http://JesusnJim.com

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Petite Abeille-2

On Sep 16, 2011, at 9:27 PM, Jim Michaels wrote:

> currently, sqlite only handles 1 row.
> INSERT INTO table(digit,dialpadstr) VALUES
> (2,'abc');

try:

insert into foo ( bar )
select 1 as bar union all
select 2 as bar
etc...

Please refer back to the fine manual:

http://www.sqlite.org/lang_insert.html
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Kees Nuyt
In reply to this post by Jim Michaels-2
On Fri, 16 Sep 2011 12:27:35 -0700, Jim Michaels
<[hidden email]> wrote:

>INSERT is supposed to handle multiple rows for VALUES.
>for example,
>INSERT INTO table(digit,dialpadstr) VALUES
>(2,'abc'),
>(3,'def'),
>(4,'ghi'),
>(5,'jkl'),
>(6,'mno'),
>(7,'pqrs'),
>(8,'tuv'),
>(9,'wxyz');

With the preferred

{
        _open*()
        :
        _exec('BEGIN')
        _prepare*()
        while rows2insert{
                _bind*()
                _bind*()
                :
                :
                _step()
                _reset() (?)
        }
        _finalize()
        _exec('COMMIT')
        :
        _close()
}
sequence, there is no need for a multirow INSERT syntax.
It would complicate the _bind()

With shell scipts, it doesn't have much advantage either, as you
can prepend the fixed part
"INSERT INTO tablename (colname1,...colnameN) VALUES "
very easily, e.g.

somepreprocess | awk -f script | sqlite3 database >log

where script contains something like:
{
        printf \
        "INSERT INTO ... VALUES (%d,'%s',...);\n",\
        $1,$2,...
}

>currently, sqlite only handles 1 row.
>INSERT INTO table(digit,dialpadstr) VALUES
>(2,'abc');
--
  (  Kees Nuyt
  )
c[_]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Igor Tandetnik
In reply to this post by Jim Michaels-2
Jim Michaels <jimm-VsnNql4zhRrV7NJZ79vff+jYdJvx94icpqFO/[hidden email]> wrote:
> INSERT is supposed to handle multiple rows for VALUES.

Supposed by whom? What is the basis for this claim?
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Simon Slavin-3
In reply to this post by Jim Michaels-2

On 16 Sep 2011, at 8:27pm, Jim Michaels wrote:

> INSERT is supposed to handle multiple rows for VALUES.
> for example,
> INSERT INTO table(digit,dialpadstr) VALUES
> (2,'abc'),
> (3,'def'),
> (4,'ghi'),
> (5,'jkl'),
> (6,'mno'),
> (7,'pqrs'),
> (8,'tuv'),
> (9,'wxyz');

Can you find support for that 'supposed' in the specification for SQL ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Puneet Kishor-2
In reply to this post by Igor Tandetnik

On Sep 17, 2011, at 10:11 AM, Igor Tandetnik wrote:

> Jim Michaels <jimm-VsnNql4zhRrV7NJZ79vff+jYdJvx94icpqFO/[hidden email]> wrote:
>> INSERT is supposed to handle multiple rows for VALUES.
>
> Supposed by whom? What is the basis for this claim?

The multiline INSERT capability may not be a SQL standard, but it is not only highly convenient, it is also supported by Pg, the ostensible role model and inspiration for SQLite. Not that I particularly care either way because I almost always use Perl to deal with Pg and SQLite, but it would/might be nice to have for some. Perhaps those folks should continue to ask Richard nicely instead of seeming to "demand" it on the basis of some political claim.

--
Puneet Kishor

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Simon Slavin-3

On 17 Sep 2011, at 4:29pm, Mr. Puneet Kishor wrote:

> The multiline INSERT capability may not be a SQL standard, but it is not only highly convenient, it is also supported by Pg, the ostensible role model and inspiration for SQLite. Not that I particularly care either way because I almost always use Perl to deal with Pg and SQLite, but it would/might be nice to have for some. Perhaps those folks should continue to ask Richard nicely instead of seeming to "demand" it on the basis of some political claim.

How would you support multiple INSERTs with binding ?  Given a statement like

> INSERT INTO table(digit,dialpadstr) VALUES
> (2,'abc'),(3,'def'),(4,'ghi'),(5,'jkl'),(6,'mno'),(7,'pqrs'),(8,'tuv'),(9,'wxyz');

would you expect 16 binding values ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Petite Abeille-2
In reply to this post by Igor Tandetnik

On Sep 17, 2011, at 5:11 PM, Igor Tandetnik wrote:

> Supposed by whom? What is the basis for this claim?

 SQL-92:

http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts

This feature is supported by DB2, SQL Server (since version 10.0 - i.e. 2008), PostgreSQL (since version 8.2), MySQL, and H2.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Puneet Kishor-2
In reply to this post by Simon Slavin-3

On Sep 17, 2011, at 10:33 AM, Simon Slavin wrote:

>
> On 17 Sep 2011, at 4:29pm, Mr. Puneet Kishor wrote:
>
>> The multiline INSERT capability may not be a SQL standard, but it is not only highly convenient, it is also supported by Pg, the ostensible role model and inspiration for SQLite. Not that I particularly care either way because I almost always use Perl to deal with Pg and SQLite, but it would/might be nice to have for some. Perhaps those folks should continue to ask Richard nicely instead of seeming to "demand" it on the basis of some political claim.
>
> How would you support multiple INSERTs with binding ?  



I wouldn't. Multiple INSERT statement seems to be better suited for large scale dump/restore, or even for manual entry, but without binding.

Essentially, if I had my druthers, I would support whatever Pg supports and be done with it. For most part SQLite seems to follow "good for Pg good for the gander" philosophy.



> Given a statement like
>
>> INSERT INTO table(digit,dialpadstr) VALUES
>> (2,'abc'),(3,'def'),(4,'ghi'),(5,'jkl'),(6,'mno'),(7,'pqrs'),(8,'tuv'),(9,'wxyz');
>
> would you expect 16 binding values ?
>
> Simon.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Petite Abeille-2

On Sep 17, 2011, at 5:55 PM, Mr. Puneet Kishor wrote:

> Essentially, if I had my druthers, I would support whatever Pg supports and be done with it. For most part SQLite seems to follow "good for Pg good for the gander" philosophy.

I would much rather have a MERGE statement:

http://en.wikipedia.org/wiki/Merge_(SQL)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Simon Slavin-3
In reply to this post by Petite Abeille-2

On 17 Sep 2011, at 4:52pm, Petite Abeille wrote:

> On Sep 17, 2011, at 5:11 PM, Igor Tandetnik wrote:
>
>> Supposed by whom? What is the basis for this claim?
>
> SQL-92:
>
> http://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts

Here's a draft of SQL-92.  You can find the INSERT statement on page 388 et seq..

<http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt>

Can you find the syntax that allows

>> INSERT INTO table(digit,dialpadstr) VALUES
>> (2,'abc'),(3,'def'),(4,'ghi'),(5,'jkl'),(6,'mno'),(7,'pqrs'),(8,'tuv'),(9,'wxyz');

?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Kees Nuyt
In reply to this post by Simon Slavin-3
On Sat, 17 Sep 2011 16:23:23 +0100, Simon Slavin
<[hidden email]> wrote:

>
>On 16 Sep 2011, at 8:27pm, Jim Michaels wrote:
>
>> INSERT is supposed to handle multiple rows for VALUES.
>> for example,
>> INSERT INTO table(digit,dialpadstr) VALUES
>> (2,'abc'),
>> (3,'def'),
>> (4,'ghi'),
>> (5,'jkl'),
>> (6,'mno'),
>> (7,'pqrs'),
>> (8,'tuv'),
>> (9,'wxyz');
>
>Can you find support for that 'supposed' in the specification for SQL ?

Probably not.

See:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Specifically:
Page 390, Chapter 13.8, leveling rules, rule 2a.
Page 648, Rule 63a.
--
  (  Kees Nuyt
  )
c[_]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Petite Abeille-2
In reply to this post by Simon Slavin-3

On Sep 17, 2011, at 6:04 PM, Simon Slavin wrote:

> Can you find the syntax that allows

These ANSI specifications are notoriously difficult to pinpoint :)

DB2:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000970.htm

INSERT
INTO         DEPARTMENT
                  (DEPTNO, DEPTNAME, ADMRDEPT)
VALUES  ('B11', 'PURCHASING', 'B01'),
                  ('E41', 'DATABASE ADMINISTRATION', 'E01')

MySQL:

http://dev.mysql.com/doc/refman/5.1/en/insert.html

INSERT INTO tbl_name
 (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);


PostgreSQL:

http://www.postgresql.org/docs/8.2/static/sql-insert.html

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');


SQL Server:

http://msdn.microsoft.com/en-us/library/dd776382.aspx

INSERT INTO Production.UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923');

Pretty consistent syntax across vendors, and not totally outlandish  to presume it's a "standard"  from the point of view of the casual dilettante :)

Sadly, there is no such thing as "standard sql" :))

Still, in the case of SQLite as with any other product, the fine manual is there to sort this out:

"SQL As Understood By SQLite"
http://www.sqlite.org/lang.html








_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Simon Slavin-3

On 17 Sep 2011, at 6:08pm, Petite Abeille wrote:

> On Sep 17, 2011, at 6:04 PM, Simon Slavin wrote:
>
>> Can you find the syntax that allows
>
> These ANSI specifications are notoriously difficult to pinpoint :)

Not really, they just cost money.  For instance:

<http://webstore.ansi.org/RecordDetail.aspx?sku=INCITS%2fISO%2fIEC+9075-1-2008>

That's part 1 and costs US$30.  For a good grasp of SQL you need most of the first 11 parts.  Part 11, for instance costs US$321.00.  Consequently nobody buys them.  And consequently nobody cares what they say.

> Pretty consistent syntax across vendors, and not totally outlandish

As was clear from my post, I was referring to SQL standards.  What various implementation vendors choose to do is up to them.  But the multi-spec syntax referred to in the OP is not in any SQL standard I've seen.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Petite Abeille-2

On Sep 17, 2011, at 7:33 PM, Simon Slavin wrote:

> As was clear from my post, I was referring to SQL standards.  What various implementation vendors choose to do is up to them.  But the multi-spec syntax referred to in the OP is not in any SQL standard I've seen.

In BNF Grammar for ISO/IEC 9075:1999 - Database Language SQL (SQL-99), under contextually typed row value expression list:

http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Simon Slavin-3

On 17 Sep 2011, at 6:42pm, Petite Abeille wrote:

> On Sep 17, 2011, at 7:33 PM, Simon Slavin wrote:
>
>> As was clear from my post, I was referring to SQL standards.  What various implementation vendors choose to do is up to them.  But the multi-spec syntax referred to in the OP is not in any SQL standard I've seen.
>
> In BNF Grammar for ISO/IEC 9075:1999 - Database Language SQL (SQL-99), under contextually typed row value expression list:
>
> http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list

  <insert columns and source>    ::=   <from subquery> | <from constructor> | <from default>

? Which one, and where is the expansion that allows for multiple sets of brackets after "VALUES" ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Petite Abeille-2

On Sep 17, 2011, at 8:06 PM, Simon Slavin wrote:

>  <insert columns and source>    ::=   <from subquery> | <from constructor> | <from default>
>
> ? Which one, and where is the expansion that allows for multiple sets of brackets after "VALUES" ?

If I'm reading this correctly, the constructor one.

Step by step:

(1)  insert statement

http://savage.net.au/SQL/sql-99.bnf.html#insert%20statement

(2)  insert columns and source

http://savage.net.au/SQL/sql-99.bnf.html#insert%20columns%20and%20source

(3) from constructor

http://savage.net.au/SQL/sql-99.bnf.html#from%20constructor

(4)  contextually typed table value constructor

http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20table%20value%20constructor

(5) contextually typed row value expression list

http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list

  <contextually typed row value expression list>    ::=
         <contextually typed row value expression>
         [ { <comma> <contextually typed row value expression> }... ]

(6) contextually typed row value constructor

         <contextually typed row value constructor element>
     |     [ ROW ] <left paren> <contextually typed row value constructor element list> <right paren>

http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20constructor

Phew.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Puneet Kishor-2
In reply to this post by Simon Slavin-3

On Sep 17, 2011, at 1:06 PM, Simon Slavin wrote:

>
> On 17 Sep 2011, at 6:42pm, Petite Abeille wrote:
>
>> On Sep 17, 2011, at 7:33 PM, Simon Slavin wrote:
>>
>>> As was clear from my post, I was referring to SQL standards.  What various implementation vendors choose to do is up to them.  But the multi-spec syntax referred to in the OP is not in any SQL standard I've seen.
>>
>> In BNF Grammar for ISO/IEC 9075:1999 - Database Language SQL (SQL-99), under contextually typed row value expression list:
>>
>> http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list
>
>  <insert columns and source>    ::=   <from subquery> | <from constructor> | <from default>
>
> ? Which one, and where is the expansion that allows for multiple sets of brackets after "VALUES" ?
>

All this chattering among us doesn't really matter (other than for academic purposes). All that matters is whether or not Richard and co. deem it worth including as a capability in factory provided SQLite. While I can't attest to it, there are probably instances where SQLite deviates from the so called standard ("the problem with standards is there are plenty of them" and all that). For me, if Pg does it, the SQLite could do it if those who make SQLite could be convinced of its usefulness.

For me, I don't care either way. I am glad Pg has it because I am trying to convert MySQL data to Pg. While I am failing to do so painlessly for the most part, a few of the (simpler) tables convert fine because both MySQL and Pg support multi-line INSERTs.

Puneet.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Petite Abeille-2

On Sep 17, 2011, at 8:43 PM, Mr. Puneet Kishor wrote:

> All this chattering among us doesn't really matter (other than for academic purposes).

For the, hmmm, more scholarly inclined on the list, a marginally more readable ISO spec, ISO/IEC 9075-2:2003:

http://synthesis.ipi.ac.ru/synthesis/student/oodb/essayRef/sqlFoundation

> All that matters is whether or not Richard and co. deem it worth including as a capability in factory provided SQLite. While I can't attest to it, there are probably instances where SQLite deviates from the so called standard ("the problem with standards is there are plenty of them" and all that). For me, if Pg does it, the SQLite could do it if those who make SQLite could be convinced of its usefulness.
>
> For me, I don't care either way. I am glad Pg has it because I am trying to convert MySQL data to Pg. While I am failing to do so painlessly for the most part, a few of the (simpler) tables convert fine because both MySQL and Pg support multi-line INSERTs.

FWIW, I personally find that specific flavor of multi-rows INSERT rather esoteric and borderline useless, but each to their own.

I would not mind seeing MERGE tough :))



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: INSERT syntax missing key SQL syntax

Simon Slavin-3
In reply to this post by Petite Abeille-2

On 17 Sep 2011, at 7:28pm, Petite Abeille wrote:

> (5) contextually typed row value expression list
>
> http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20expression%20list
>
>  <contextually typed row value expression list>    ::=
>         <contextually typed row value expression>
>         [ { <comma> <contextually typed row value expression> }... ]
>
> (6) contextually typed row value constructor
>
>         <contextually typed row value constructor element>
>     |     [ ROW ] <left paren> <contextually typed row value constructor element list> <right paren>
>
> http://savage.net.au/SQL/sql-99.bnf.html#contextually%20typed%20row%20value%20constructor

Hmm.  Okay I didn't interpret that that way.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users