Re: SQL: NOT NULL DEFAULT

[CRPence <CRPbottle@xxxxxxxxx>]

  DEFAULT is allowed in the VALUES for INSERT INTO.  For a value in a 
full-select, specify a literal, special register, or function.  So for 
the second example that works, that could be done instead of with 
literals, using:

<code>
 insert into to-file
   (select F.* , USER , CURRENT TIMESTAMP
    from from-file F
    where ...
   )
</code>

  Effectively the same as:

<code>
 create table fromfile (a char, b int)
 ;
 create table tofile like fromfile rcdfmt rcdfmtname
 ;
 alter table tofile
  add column createdby varchar(18) not null with default USER
  add column createdat timestamp not null with default CURRENT TIMESTAMP
 ;
 insert into tofile (a, b)    -- specify column list for insert
  (select F.* from fromfile F -- F.* is column list (a, b)
   where ... )
 ;
</code>

  When the columns are omitted on the insert, the default will be 
established for each row inserted in those omitted columns.

Regards, Chuck

Glenn Gundermann wrote:
> I'm not absolutely sure about the creation of a table with regards to
> whether I should specify NOT NULL or not and how to take advantage of
> the DEFAULT.
>
> Basically, I want to be able to do the following:
>
> I have to-file which is a copy of from-file plus two fields, user & 
> timestamp, created by:
>
> /* Create table. */
> CREATE TABLE to-file LIKE from-file
>   RCDFMT rcdfmt-name ;
> ALTER TABLE to-file ADD COLUMN H3_CREATED_BY FOR COLUMN H3CRTUSER
> VARCHAR(18) NOT NULL DEFAULT USER;
> ALTER TABLE to-file ADD COLUMN H3_CREATED_TIMESTAMP FOR COLUMN
> H3CRTTIMST TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
>
> Now I want to copy records from from-file to to-file using:
>
> insert into to-file
>  (select F.* , DEFAULT , DEFAULT from from-file F
>   where F.h3ipco = '61' and
>         F.h3kfdt = 1030123 and
>         F.h3c9tm = 202631 and
>         F.h3ksnr = 1);
>
> This does not work, giving me "Column DEFAULT not in specified tables."
>
> The following does work though:
>
> insert into to-file
>  (select F.* , 'KNCZIAGG' , '2008-06-20-13.21.00.000000' from from-file F
>   where F.h3ipco = '61' and
>         F.h3kfdt = 1030123 and
>         F.h3c9tm = 202631 and
>         F.h3ksnr = 1);
>
> Do I need/want NOT NULL in my CREATE TABLE statement?
> How can I take advantage of DEFAULT in my INSERT statement?