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

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

>Solution :

Do that in USING clause.

Setting date format (so that you’d know what is what; you don’t have to do it):

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

Use TO_DATE with appropriate format mask on e_date; t_ref_num looks like an ordinary integer, so I’m just TO_NUMBER-ing it.

SQL> MERGE INTO tab2 tt
  2       USING (SELECT e_id,
  3                     TO_DATE (e_date, 'dd-mm-yyyy') e_date,
  4                     TO_NUMBER (t_ref_num) t_ref_num
  5                FROM tab1) t1
  6          ON (tt.e_id = t1.e_id)
  7  WHEN MATCHED
  8  THEN
  9     UPDATE SET tt.e_date = t1.e_date, tt.t_ref_num = t1.t_ref_num
 10  WHEN NOT MATCHED
 11  THEN
 12     INSERT     (e_id, e_date, t_ref_num)
 13         VALUES (t1.e_id, t1.e_date, t1.t_ref_num);

3 rows merged.

Result:

SQL> select * from tab2;

      E_ID E_DATE      T_REF_NUM
---------- ---------- ----------
         3 01.01.2002         13
         1 01.01.2000         11
         2 01.01.2001         12

SQL>