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?
