RE: SQL: NOT NULL DEFAULT

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

I think Chuck was saying that you'll have to specify the column definition
list for that option to work.
That means that you'd have to spell out all of the column names your 'SELECT
*' is populating and let the others default, i.e.

insert into to-file
 (field1, field2, field3, field4, field5, ......)
 (select * 
  from from-file
  where h3ipco = '61' and
        h3kfdt = 1030123 and
        h3c9tm = 202631 and
        h3ksnr = 1);

The part (field1,field2,...) is called the column definition list, and if
you omit the 'default taker' columns from the column definition list, you'll
get the behavior you desired.

Drawback of this approach is that you have to specify all of the columns
populated by 'SELECT *', and if there are a lot of them, it turns into a
'chore' pretty easily.

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: SQL: NOT NULL DEFAULT

Hi Chuck,

When I omit the user and time stamp columns, I get a message saying the
number of values don't match.

Tks, Glenn


<snip>
>    When the columns are omitted on the insert, the default will be
> established for each row inserted in those omitted columns.
>
> Regards, Chuck