By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,557 Members | 1,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,557 IT Pros & Developers. It's quick & easy.

Syntactic sugar for INSERT

P: n/a
This is probably the wrong forum for this, but I thought it might start
some discussion.

The INSERT statement, in its current form, has problems being formatted
so a human reader can follow it. In particular, for a table with many
columns, the only to put the values in close proximity with the column
names is horizontally, which can lead to lines of 1000s of characters in
length which are difficult to read and edit. What I would like to see is
an optional from of the INSERT statement with syntax like the SET clause
of the UPDATE statement, which keeps the values in close proximity with
the column names and also allows reasonable length lines for human
reading and editing. Clearly this is just syntactic sugar because the
new syntax does not add any power or functionality to the INSERT statement.
Nov 12 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Bob Stearns wrote:
This is probably the wrong forum for this, but I thought it might start
some discussion.

The INSERT statement, in its current form, has problems being formatted
so a human reader can follow it. In particular, for a table with many
columns, the only to put the values in close proximity with the column
names is horizontally, which can lead to lines of 1000s of characters in
length which are difficult to read and edit. What I would like to see is
an optional from of the INSERT statement with syntax like the SET clause
of the UPDATE statement, which keeps the values in close proximity with
the column names and also allows reasonable length lines for human
reading and editing. Clearly this is just syntactic sugar because the
new syntax does not add any power or functionality to the INSERT
statement.

So you want something similar to a "natural" join, just as a natural insert.

INSERT INTO T NATURAL SELECT x AS a, Y as b FROM T
with a and b being columns of T in some order.

Note that a natural join is a join in which columns with the same are
matched.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
Serge Rielau wrote:
Bob Stearns wrote:
This is probably the wrong forum for this, but I thought it might
start some discussion.

The INSERT statement, in its current form, has problems being
formatted so a human reader can follow it. In particular, for a table
with many columns, the only to put the values in close proximity with
the column names is horizontally, which can lead to lines of 1000s of
characters in length which are difficult to read and edit. What I
would like to see is an optional from of the INSERT statement with
syntax like the SET clause of the UPDATE statement, which keeps the
values in close proximity with the column names and also allows
reasonable length lines for human reading and editing. Clearly this is
just syntactic sugar because the new syntax does not add any power or
functionality to the INSERT statement.


So you want something similar to a "natural" join, just as a natural
insert.

INSERT INTO T NATURAL SELECT x AS a, Y as b FROM T
with a and b being columns of T in some order.

Note that a natural join is a join in which columns with the same are
matched.

Cheers
Serge

That appears to be just what I was thinking about. Unfortunately, I can
find no reference to 'NATURAL' either in the SQL Reference manuals or in
the reference material at the infocenter. The behavior you indicate is,
of course, the desired one, but I would like to read more about the
NATURAL modifier especially since this is not a join.
Nov 12 '05 #3

P: n/a
Bob Stearns wrote:
That appears to be just what I was thinking about. Unfortunately, I can
find no reference to 'NATURAL' either in the SQL Reference manuals or in
the reference material at the infocenter. The behavior you indicate is,
of course, the desired one, but I would like to read more about the
NATURAL modifier especially since this is not a join.


Is there also an UNNATURAL select? ;-)

Dan
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)
Nov 12 '05 #4

P: n/a
DA Morgan wrote:
Bob Stearns wrote:
That appears to be just what I was thinking about. Unfortunately, I
can find no reference to 'NATURAL' either in the SQL Reference manuals
or in the reference material at the infocenter. The behavior you
indicate is, of course, the desired one, but I would like to read more
about the NATURAL modifier especially since this is not a join.

Is there also an UNNATURAL select? ;-)

I guess the "defender of [geese and] all things natural" (Larry M.)
would know.

Bob,

The concept of a natural join is actually in the SQL Standard, but it's
not supported by DB2. To be honest I can think of about dozens of things
I'd like to see before I'd start worrying about that sort of sugar....

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #5

P: n/a
Serge Rielau wrote:
DA Morgan wrote:
Bob Stearns wrote:
That appears to be just what I was thinking about. Unfortunately, I
can find no reference to 'NATURAL' either in the SQL Reference
manuals or in the reference material at the infocenter. The behavior
you indicate is, of course, the desired one, but I would like to read
more about the NATURAL modifier especially since this is not a join.


Is there also an UNNATURAL select? ;-)

I guess the "defender of [geese and] all things natural" (Larry M.)
would know.

Bob,

The concept of a natural join is actually in the SQL Standard, but it's
not supported by DB2. To be honest I can think of about dozens of things
I'd like to see before I'd start worrying about that sort of sugar....

Cheers
Serge

Is there a standard DB2 schema.table (containing only one row) one can
use for selecting constants from. That could be used for this purpose,
modulo the convenience of column reordering.
Nov 12 '05 #6

P: n/a
Bob Stearns wrote:
Serge Rielau wrote:
DA Morgan wrote:
Bob Stearns wrote:

That appears to be just what I was thinking about. Unfortunately, I
can find no reference to 'NATURAL' either in the SQL Reference
manuals or in the reference material at the infocenter. The behavior
you indicate is, of course, the desired one, but I would like to
read more about the NATURAL modifier especially since this is not a
join.


Is there also an UNNATURAL select? ;-)

I guess the "defender of [geese and] all things natural" (Larry M.)
would know.

Bob,

The concept of a natural join is actually in the SQL Standard, but
it's not supported by DB2. To be honest I can think of about dozens of
things I'd like to see before I'd start worrying about that sort of
sugar....

Cheers
Serge


Is there a standard DB2 schema.table (containing only one row) one can
use for selecting constants from. That could be used for this purpose,
modulo the convenience of column reordering.


Something similar to DUAL in Oracle.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)
Nov 12 '05 #7

P: n/a
There is "sysibm.sysdummy1" which contains only one row with one value:
'Y'. But you can achieve the same using a "select * from
table(values('Y')) as t1(val1)", where t1 is the table name and val1
the name of the firts column. I hope this is what you were looking for.

Janick

Nov 12 '05 #8

P: n/a
Janick Bernet wrote:
There is "sysibm.sysdummy1" which contains only one row with one value:
'Y'. But you can achieve the same using a "select * from
table(values('Y')) as t1(val1)", where t1 is the table name and val1
the name of the firts column. I hope this is what you were looking for.

Janick

I recently gave a talk where 25% of my time was spent on VALUES.
VALUES is supported since DB2 V2 and it is part of the SQL staandard,
yet it is unknown to shockingly many....

Quick rundown:
VALUES is a table constructure.
VALUES (1)
=>
((1))
VALUES (1, 2), (3, 4)
=> ((1, 2), (3, 4))
VALUES (CAST(? AS INT), CAST(? AS FLOAT)), (?, ?), (?, ?)
=> Finds in three rows from the application.
Note that teh ? of one (the first for convenience) row need to be cast
to establish data types.

You can use VALUES ANYWHERE(!) a SELECT is allowed.
The TABLE (or LATERAL) keyword is used to allow "lateral" correlation.
So you can do things like PIVOT or multi row INSERT:

CREATE TABLE sales(year INT, q1 INT, q2 INT, q3 INT, q4 INT);
INSERT INTO sales VALUES
(2001, 20, 30, 15, 10),
(2002, 35, 31, 29, 19),
(2002, 41, 43, 38, 25);

SELECT S.year, Q.quarter, Q.sales
FROM sales AS S,
TABLE(VALUES(1, S.q1),
(2, S.q2),
(3, S.q3),
(4, S.q4))
AS Q(quarter, sales);

YEAR QUARTER SALES
----------- ----------- -----------
2001 1 20
2001 2 30
2001 3 15
2001 4 10
2002 1 35
2002 2 31
2002 3 29
2002 4 19
2002 1 41
2002 2 43
2002 3 38
2002 4 25

12 record(s) selected.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #9

P: n/a
I actually prefer (with tabbing not shown here):

INSERT INTO
A
(
Col1,
Col2,
Col3,
Col4,
Col5
)
VALUES
(
Val1,
Val2,
Val3,
Val4,
Val5
)

Especially when instead of a VALUES() an INSERT is used, i find this
wonderfully readable and a lot easier for quick formatting than
anything else. (I format nearly everything in a monospace font with
tabs.)

B.

Nov 12 '05 #10

P: n/a
Brian Tkatch wrote:
I actually prefer (with tabbing not shown here):

INSERT INTO
A
(
Col1,
Col2,
Col3,
Col4,
Col5
)
VALUES
(
Val1,
Val2,
Val3,
Val4,
Val5
)

Especially when instead of a VALUES() an INSERT is used, i find this
wonderfully readable and a lot easier for quick formatting than
anything else. (I format nearly everything in a monospace font with
tabs.)

B.

That works well if the list is less than one video page, but is
miserable to check / change when the list goes over one page. I'm
looking for human usability; someone (Weinberg, I think) said the
purpose of a program is not to communicate with a computer but rather
with the following programmer, even if it yourself.
Nov 12 '05 #11

P: n/a
>> VALUES (CAST(? AS INT), CAST(? AS FLOAT)), (?, ?), (?, ?)
=> Finds in three rows from the application. Note that the ? of one
(the first for convenience) row need to be cast to establish data
types. <<

That is a trick I did not know!

Don't forget that you can put scalar subquery expresions in the
VALUES() list, too.

Nov 12 '05 #12

P: n/a
> but is miserable to check / change when the list goes over one page.

Not really. Personally, i use notepad, and opening up a second window
is so easy, comparison is a snap. A spreadsheet makes this *very* easy
when it is in a column, and a couple of un*x commands could do the
same. IOW, with a little bit of work, the columnized version works very
well.

An added plus, is that the statement is split into two clear sections,
making them modular should a similar statement be needed. This would be
lost in the proposed solution, as would an INSERT that relies on COLUMN
order. While normally that is a Bad Idea (tm), it works very well for
keeping two data sources in sync (import/export or cross-schema) and
two TABLEs CREATEd with the same statement.

Admittedly, SQL itself is inconsistent. The proposal is nice, and it
would make some code easier to write, especially when the requirement
is to "INSERT it if it isn't there/UPDATE if it is". Perhaps the
propsal would be better to allow INSERT formatting for UPDATE and
vice-versa, then it would simply be the programmer's choice.

B.

Nov 12 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.