Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need to convert the data type while inserting into the other table

Albert ChaoNov 24 2021
CREATE TABLE tab1 (
    e_id    VARCHAR2(255),
    e_date  VARCHAR2(255),
    t_ref_num  VARCHAR2(255),
    CONSTRAINT pk_tab1 PRIMARY KEY ( e_id )
);

    INSERT INTO tab1 VALUES (
    1,
    '01-01-2000',
    11
);

INSERT INTO tab1 VALUES (
    2,
    '01-01-2001',
    12
);

INSERT INTO tab1 VALUES (
    3,
    '01-01-2002',
    13
);

CREATE TABLE tab2 (
    e_id       NUMBER(20),
    e_date     DATE,
    t_ref_num  NUMBER(20),
    CONSTRAINT pk_tab2 PRIMARY KEY ( e_id )
);

I need to insert it into the tab2 table and need to convert the data type as per the tab2 table datatype. Because in tab1 table I will get always varchar datatype but while inserting into the tab2 table I have to typecast it and have to insert in proper datatype. How can I achieve this?

MERGE INTO tab2 tt
USING (
          SELECT
              e_id,
              e_date,
              _t_ref_num
          FROM
              tab1
      )
t1 ON ( t2.e_id = t1.e_id )
WHEN MATCHED THEN UPDATE
SET tt.e_date = t1.e_date,
    tt.t_ref_num = t1.t_ref_num
WHEN NOT MATCHED THEN
INSERT (
    e_id,
    e_date,
    t_ref_num )
VALUES
    ( t1.e_id,
      t1.e_date,
      t1.t_ref_num );

I have to make the changes in the merge statement only

This post has been answered by BluShadow on Nov 24 2021
Jump to Answer
Comments
Post Details
Added on Nov 24 2021
6 comments
1,313 views