Product Suggestion: INSERT SET

In the real world, many (almost all?) production INSERT statements look like this:

Syntax 1: INSERT table-name [ ( column-name, ... ) ] VALUES ( expression, ... )

   or

Syntax 2: INSERT table-name [ ( column-name, ... ) ] SELECT expression, ... FROM ...

In the real world, tables have dozens (hundreds?) of columns, and it is a real chore matching up the ( column-name ) list with the expression list.

It gets worse if you leave out the ( column-name ) list: you have to match the expression list up with the full CREATE TABLE column list, plus all the columns that have been added over the years with ALTER TABLE.

No such problem exists for the UPDATE statement; the column names and expressions are matched up in pairs:

UPDATE table-name SET column-name = expression, ...

SQL Anywhere needs the same thing:

Syntax 3: INSERT table-name SET column-name = expression, ...

That's the simple, real-world version of Syntax 3: INSERT SET.

For a more [ahem] sophisticated version of this suggestion, you can visit the Year 2002 in the Network News Tar Pit:

Newsgroups: sybase.public.sqlanywhere.product_futures_discussion
From: Breck Carter
Date: Sat, 13 Jul 2002 11:04:22 -0400
Local: Sat, Jul 13 2002 11:04 am
Subject: Insert Syntax 3

Insert statements involving long column name lists are difficult to 
write and maintain because the corresponding VALUES list (Insert 
Syntax 1) or select statement (Syntax 2) are coded separately. It's 
just too easy to get names and values in the wrong order and if the 
datatypes match there's no error message at runtime. 

The following "Insert Syntax 3" uses the more maintainable "SET column 
= value" syntax from the UPDATE statement: 

Insert Syntax 3 

INSERT [ INTO ] [ owner.]table-name SET set-item, ... 
[ FROM table-list ] 
[ WHERE search-condition ] 
[ ORDER BY expression [ ASC | DESC ], ... ] 

set-item : 
  column-name [.field-name...] = expression 
| column-name [.field-name...] = DEFAULT 
| column-name[.field-name...].method-name( [ expression ] ) 

Note: The special keyword "DEFAULT" should be allowed on the right 
side of a set-item. 

Note: The "@variable-name = expression" set-item is probably not 
necessary for INSERT. 

Insert Syntax 3 would be in addition to, NOT replacing, the current 
two formats: 

Insert Syntax 1 

INSERT [ INTO ] [ owner.]table-name [ ( column-name, ... ) ] 
VALUES ( expression | DEFAULT, ... ) 

Insert Syntax 2 

INSERT [ INTO ] [ owner.]table-name [ ( column-name, ... ) ] 
select-statement 

===== 

For the record, here's the corresponding Update syntax: 

Update Syntax 1 

UPDATE [ FIRST | TOP n ] table-list SET set-item, ... 
[ FROM table-list ] 
[ WHERE search-condition ] 
[ ORDER BY expression [ ASC | DESC ], ... ] 

set-item : 
  column-name [.field-name...] = expression 
| column-name[.field-name...].method-name( [ expression ] ) 
| @variable-name = expression 

==================================================================

Newsgroups: sybase.public.sqlanywhere.product_futures_discussion
From: "Richard Biffl"
Date: Sat, 13 Jul 2002 23:11:13 -0400
Local: Sat, Jul 13 2002 11:11 pm
Subject: Re: Insert Syntax 3

Allowing SET in an INSERT statement would make the statement more readable 
and maintainable -- though I'd probably stick with the old obscure standard 
form because it's standard -- but I don't see the function of the FROM, 
WHERE, and ORDER BY clauses to an INSERT statement, other than allowing your 
other, more controversial <g>, suggestion (INSERT OR UPDATE). 

==================================================================

Newsgroups: sybase.public.sqlanywhere.product_futures_discussion
From: "Paul Horan[TeamSybase]" 
Date: Sat, 13 Jul 2002 23:24:46 -0400
Local: Sat, Jul 13 2002 11:24 pm
Subject: Re: Insert Syntax 3

I think this borders on genius, personally...  Well done, Breck! 

You would need the FROM/WHERE/ORDER BY clauses because you're basically 
writing the SELECT statement from syntax 2, just splitting out the SELECT 
list from that statement into the separate SET statements. 

So, instead of 

INSERT into LongTable 
 (column list....) 
SELECT (expression list that hopefully lines up) 
  FROM OtherTable 
WHERE <condition> 
ORDER BY 1,2,3 

you would have: 

INSERT into LongTable 
  SET col1 = <previous SELECT's 1st expression>, 
  SET col2 = <previous SELECT's 2nd expression>, 
  SET col3 = DEFAULT, 
... 
FROM OtherTable 
WHERE <condition> 
ORDER BY 1,2,3 

Umm, yes please... 

Did he just say "genius"?

---