db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r546492 - /db/derby/docs/trunk/src/ref/rrefsqlj24513.dita
Date Tue, 12 Jun 2007 14:17:49 GMT
Author: bpendleton
Date: Tue Jun 12 07:17:48 2007
New Revision: 546492

URL: http://svn.apache.org/viewvc?view=rev&rev=546492
Log:
DERBY-2593: Add docs for the CREATE TABLE AS SUBQUERY statement

This change modifies the CREATE TABLE page in the reference guide
to add information about the as-subquery form of the CREATE TABLE
statement. This alternate form of the CREATE TABLE statement was
added by DERBY-64, and enables a table to be created by specifying
a query expression. The result columns of the query expression
are used as a model to provide the data types, and optionally the
names, of the columns in the new table.

Modified:
    db/derby/docs/trunk/src/ref/rrefsqlj24513.dita

Modified: db/derby/docs/trunk/src/ref/rrefsqlj24513.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj24513.dita?view=diff&rev=546492&r1=546491&r2=546492
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj24513.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj24513.dita Tue Jun 12 07:17:48 2007
@@ -46,11 +46,23 @@
 can specify storage properties such as page size for a table by calling the <codeph>SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY</codeph>
system
 procedure.</p><p>If a qualified table name is specified, the schema name cannot
 begin with <i>SYS</i>.</p></section>
-<refsyn><title>Syntax</title><codeblock><b>CREATE TABLE <i><xref
href="rreftablename.dita#rreftablename">table-Name</xref></i>
-    ( {<i><xref href="rrefsqlj30540.dita#rrefsqlj30540">column-definition</xref></i>
| <i><xref
+<refsyn><title>Syntax</title>
+    <p>There are two different variants of the CREATE TABLE statement,
+        depending on whether you are specifying the column definitions and
+        constraints, or whether you are modeling the columns after the
+        results of a query expression:</p>
+    <codeblock><b>CREATE TABLE <i><xref href="rreftablename.dita#rreftablename">table-Name</xref></i></b>
+  {
+      ( {<i><xref href="rrefsqlj30540.dita#rrefsqlj30540">column-definition</xref></i>
| <i><xref
 href="rrefsqlj42154.dita#rrefsqlj42154">Table-level constraint</xref></i>}
-    [ , {<i><xref href="rrefsqlj30540.dita#rrefsqlj30540">column-definition</xref></i>
| <i><xref
-href="rrefsqlj42154.dita#rrefsqlj42154">Table-level constraint</xref></i>}
] * )</b></codeblock> </refsyn>
+      [ , {<i><xref href="rrefsqlj30540.dita#rrefsqlj30540">column-definition</xref></i>
| <i><xref
+              href="rrefsqlj42154.dita#rrefsqlj42154">Table-level constraint</xref></i>}
] * )
+  |
+      [ ( <i><xref href="rrefsimplecolumnname.dita#rrefsimplecolumnname">column-name</xref></i>
[ , <i><xref href="rrefsimplecolumnname.dita#rrefsimplecolumnname">column-name</xref></i>
] * ) ]
+      <b>AS</b> <i>query-expression</i>
+      <b>WITH NO DATA</b>
+   }
+</codeblock></refsyn>
 <example><title>Example</title><codeblock><b>CREATE TABLE HOTELAVAILABILITY
      (HOTEL_ID INT NOT NULL, BOOKING_DATE DATE NOT NULL,
 	ROOMS_TAKEN INT DEFAULT 0, PRIMARY KEY (HOTEL_ID, BOOKING_DATE));
@@ -71,5 +83,44 @@
 	(START WITH 5, INCREMENT BY 5), ADDRESS VARCHAR(100), PHONE VARCHAR(15));</b></codeblock><note>For
 more examples of CREATE TABLE statements using the various constraints, see <xref
 href="rrefsqlj13590.dita#rrefsqlj13590"></xref>.</note> </example>
+<section><title>CREATE TABLE ... AS ... </title>
+    <p>With the alternate form of the CREATE TABLE statement, the column
+        names and/or the column data types can be specified by providing
+        a query. The columns in the query result are used as a model for
+        creating the columns in the new table.</p>
+<p>If no column names are specified for the new table, then all the columns
+    in the result of the query expression are used to create same-named
+    columns in the new table, of the corresponding data type(s).
+    If one or more column names are specified
+    for the new table, then the same number of columns must be present in
+    the result of the query expression; the data types of those columns are
+    used for the corresponding columns of the new table.</p>
+<p>The WITH NO DATA clause specifies that the data rows which result
+    from evaluating the query expression are not used; only the names and
+    data types of the columns in the query result are used. The
+    WITH NO DATA clause <b>must</b> be specified; in a future release,
+    Derby may be modified to allow the WITH DATA clause to be provided,
+    which would indicate that the results of the query expression should
+    be inserted into the newly-created table. In the current release, however,
+    only the WITH NO DATA form of the statement is accepted.</p>
+</section>
+<example><title>Example</title>
+    <codeblock><b>
+<ph>-- create a new table using all the columns and data types 
+-- from an existing table:</ph>
+CREATE TABLE T3 AS SELECT * FROM T1 WITH NO DATA;
+<ph>-- create a new table, providing new names for the columns, but
+-- using the data types from the columns of an existing table:</ph>
+CREATE TABLE T3 (A,B,C,D,E) AS SELECT * FROM T1 WITH NO DATA;
+<ph>-- create a new table, providing new names for the columns,
+-- using the data types from the indicated columns of an existing table:</ph>
+CREATE TABLE T3 (A,B,C) AS SELECT V,DP,I FROM T1 WITH NO DATA;
+<ph>-- This example shows that the columns in the result of the
+-- query expression may be unnamed expressions, but their data
+-- types can still be used to provide the data types for the
+-- corresponding named columns in the newly-created table:</ph>
+CREATE TABLE T3 (X,Y) AS SELECT 2*I,2.0*F FROM T1 WITH NO DATA;
+</b></codeblock>
+</example>
 </refbody>
 </reference>



Mime
View raw message