All Forums Database
DRossmann 5 posts Joined 11/11
03 Feb 2016
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.

dnoeth 4628 posts Joined 11/04
03 Feb 2016

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...

Dieter

You must sign in to leave a comment.