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