Create Table Using SELECT Statement With Default User and Date

Database
Enthusiast

Create Table Using SELECT Statement With Default User and Date

I'm trying to create a table, using a SELECT state, but that also has the LOAD_USER and LOAD_DATE with defaults to CURRENT_USER and CURRENT_TIMESTAMP.  If I was creating the table, then adding the data, the text for the two fields would be;

ADD LOAD_USER2 CHAR(20) CHARACTER SET LATIN CASESPECIFIC DEFAULT USER,

ADD LOAD_DATE2 TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6)

However, I can't figure out using a SELECT statement.  For example;

CREATE TABLE 

DATA_TABLES.TABLE_TEST

AS

(SELECT

'Mickey Mouse' AS NAME

,CURRENT_USER AS LOAD_USER 

,CURRENT_TIMESTAMP AS LOAD_DATE

)

WITH DATA

This will put the current user and date in the fields, but the fields are not set to default them going forward.

Tags (2)
1 REPLY
Junior Contributor

Re: Create Table Using SELECT Statement With Default User and Date

You can add constraints to the CREATE like

CREATE TABLE 

TABLE_TEST
(NAME,
LOAD_USER CASESPECIFIC DEFAULT USER,
LOAD_DATE DEFAULT CURRENT_TIMESTAMP(6)
)
AS
(SELECT
'Mickey Mouse' AS NAME
,TRANSLATE(CURRENT_USER USING unicode_to_latin) AS LOAD_USER
,CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS LOAD_DATE
)
WITH DATA

But then it's quite similar to a seperate CREATE TABLE followed by a INSERT/SELECT...