Re: Insert multiple rows with a single statement

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 11 Aug 2007 07:16:52 -0700
Message-ID: <1186841812.176626@bubbleator.drizzle.com>

Dieter Noeth wrote:
> sybrandb_at_hccnet.nl wrote:
>

>>> No, he's probably just asking if Oracle supports Standard SQL "table 
>>> value constructors":
>>>
>>> insert into onsite_support_costs
>>> (SITE_ID, LB_CODE, ONSITE_SUPPORT_COST)
>>> values
>>> ('Site12284','HZ10',1944.96),
>>> ('Site14041','JH96',1728.85),
>>> ('Site14942','JV98',28208.94),
>>> ...;
>>>
>>> Obviously it's not supported :-)
>>>
>>> Dieter
>>
>> There is no such thing as 'SQL standard'. There is a SQL standard with
>> various levels of conformance.

>
> OK, let's call it
> SQL:2003 optional feature F641 "Row and table constructors"

The keyword here is "optional."

> E.g. DB2 supports it, too.

Some week when you've nothing else to do put together a list of every "optional" feature in the standard and who doesn't have it. It is a very very long list. No vendor has implemented all of the optional items.

>> There is no reason to believe the SQL in the toy product MySQL is
>> 'THE' Standard.

>
> Neither Oracle SQL.

MySQL is a toy. Not because of some obscure syntax is or is not implemented. But rather because it is essentially impossible to make it comply with the law for most database uses. You'll not get it to comply with Sarbanes-Oxley, FACTA, PIPEDA, Basel II, PCI, Gramm-Leach- Bliley (GLB), Japanese privacy and banking regulations, etc.

You may not like this fact but a car that doesn't comply with the law can not be driven down the street and is referred to as a toy. An airplane that does not comply with the law and be flown is also know as a toy.

> And even Oracle moves closer to Standard SQL, e.g. JOIN syntax and CASE,
> maybe even VARCHAR will be implemented the right way in Oracle 12 :-)
>
> Dieter

All product vendors implement optional parts of the standard when their customer base demonstrate an interest. That this optional feature has not been implemented indicates Oracle's customers don't care.

It is not reasonable, however, to believe that MySQL's customers have no interest in governance, auditing, and compliance.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Aug 11 2007 - 09:16:52 CDT