473,320 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Syntactic sugar for INSERT

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
12 1586
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
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
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
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
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
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
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
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
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
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
>> 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
> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Christopher T King | last post by:
Okay, so this is really two requests in one, and they're both kinda outlandish, but I'm gonna post them nonetheless: I've always thought xrange() to be ugly; it looks to be a lot of typing just...
5
by: F Jamitzky | last post by:
It is rather easy to define functions in python that mimic the special ruby syntactic sugar like: 5.times { print "Hello World!" } or .each { |food| eat food } In python these fragments...
13
by: Neil Zanella | last post by:
Hello, It seems to me that C# properties are nothing more than syntactic sugar for getters and setters. I wonder whether others hold a different point of view. Basically, what more do they have...
4
by: Bas | last post by:
Hi group, just out of curiosity, is there a list of all the syntactic sugar that is used in python? If there isn't such a list, could it be put on a wiki somewhere? The bit of sugar that I do...
34
by: glomde | last post by:
i I would like to extend python so that you could create hiercical tree structures (XML, HTML etc) easier and that resulting code would be more readable than how you write today with packages like...
13
by: Sam Kong | last post by:
Hi, While discussing C#'s using statement, a guy and I had an argument. In C# spec (15.13), there's an explanation like the following. using (R r1 = new R()) { r1.F(); } is precisely...
11
by: Helmut Jarausch | last post by:
Hi, are decorators more than just syntactic sugar in python 2.x and what about python 3k ? How can I find out the predefined decorators? Many thanks for your help, Helmut Jarausch
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.