A dumb question

Database
Enthusiast

A dumb question

Hello There,

Is is poosible to do this in teradata

 

INSERT INTO db.TableA

SELECT * FROM db.TableB (Plus additional columns)---> CURRENT_DATETIME, HARDCODED_VALUE (a flag)

 

Consedering the two columns CURRENT_DATETIME, HARDCODED_VALUE  are present at the end of db.TableA

 

Thanks,

 

 

 

 

 

 


Accepted Solutions
Teradata Employee

Re: A dumb question

Just need to list the columns in the order desired instead of using *.

 

INSERT INTO target SELECT c1, c2, c3,101, c4 from source;

1 ACCEPTED SOLUTION
4 REPLIES
Senior Apprentice

Re: A dumb question

Hi,

 

Probably. I think what you're trying to do is:

- assume TableA has 7 columns

- assume TableB has 5 columns (the first 5 in TableA)

 

You can code the following:

INSERT INTO TableA
SELECT B.*
  ,current_timestamp
  ,hardcoded_value
FROM TableB AS B;

You probably don't need the table alias (B) either.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: A dumb question

SOURCE TABLE DDL

CREATE MULTISET TABLE SRC_TBL.TABLE_A ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      COL_X NUMBER(5,0),
      COL_Y VARCHAR(10),
      COL_Z VARCHAR(10),
      LOAD_DS TIMESTAMP(0) WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP(0)
     
      )

PRIMARY INDEX PI_XYZ_ABC ( COL_X );


TARGET TABLE DDL
CREATE MULTISET TABLE TGT_TBL.TABLE_B ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      COL_X NUMBER(5,0),
      COL_Y VARCHAR(10),
      COL_Z VARCHAR(10),
   SRC_INDICATOR NUMBER(5,0),
      LOAD_DS TIMESTAMP(0) WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP(0)
     
      )

PRIMARY INDEX PI1_XYZ1_ABC1 ( COL_X );


SOURCE TABLE DATA
COL_X|COL_Y|COL_Z|LOAD_DS
1|Newyork|Seattle|Dallas|2018-07-28 02:43:01-07:00
2|Newyork|Seattle|Dallas|2018-07-28 02:43:01-07:00
3|Newyork|Seattle|Dallas|2018-07-28 02:43:01-07:00
4|Newyork|Seattle|Dallas|2018-07-28 02:43:01-07:00


REQUIRED TARGET DATA
COL_X|COL_Y|COL_Z|SRC_INDICATOR|LOAD_DS
1|Newyork|Seattle|Dallas|101|2018-07-28 02:43:01-07:00
2|Newyork|Seattle|Dallas|101|2018-07-28 02:43:01-07:00
3|Newyork|Seattle|Dallas|101|2018-07-28 02:43:01-07:00
4|Newyork|Seattle|Dallas|101|2018-07-28 02:43:01-07:00

Wanted to pass the SRC_INDICATOR as '101'
while inserting to table B

Enthusiast

Re: A dumb question

It worked with Dave's query mentioned in the solution above if the hard coded value need to be inserted at the end of the table column / last column.

i am trying if i can insert the src_indicator in 2nd position or any position i desire in the target table where the column name is src_indicator, Any suggestion would be helpful...

 

 

Teradata Employee

Re: A dumb question

Just need to list the columns in the order desired instead of using *.

 

INSERT INTO target SELECT c1, c2, c3,101, c4 from source;