Oracle SQL distinct query

Hello All,

I have two tables in oracle database.
1) MOTDC_STG_SHIP_CONF_HEADER_TMP
2) MOTDC_STG_SHIP_CONF_HEADER

Both tables are identical, only first table (MOTDC_STG_SHIP_CONF_HEADER_TMP)  does not have any unique constraint and has lots of duplicate data.  And 2nd Main table has unique contraint on transaction_id, CONTAINER_ID, CONTAINER_TYPE, and does not have duplicate data.  

I want to select all distinct records based on transaction_id, CONTAINER_ID, CONTAINER_TYPE from MOTDC_STG_SHIP_CONF_HEADER_TMP table and insert into the MOTDC_STG_SHIP_CONF_HEADER table.

Schema for MOTDC_STG_SHIP_CONTAINER

CREATE TABLE MOTDC_STG_SHIP_CONTAINER
(
  TRANSACTION_ID       NUMBER,
  CONTAINER_TYPE       VARCHAR2(10 BYTE),
  CONTAINER_ID         VARCHAR2(40 BYTE),
  CONTAINER_LENGTH     NUMBER(9,3),
  CONTAINER_WIDTH      NUMBER(9,3),
  CONTAINER_HEIGHT     NUMBER(9,3),
  CONTAINER_WEIGHT     NUMBER(9,3)              NOT NULL,
  CONTAINER_PACK_TYPE  VARCHAR2(10 BYTE),
  CONTAINER_TRK_NUM    VARCHAR2(50 BYTE)        NOT NULL,
  CREATION_DATE        DATE                     NOT NULL,
  CREATED_BY           NUMBER                   NOT NULL,
  LAST_UPDATE_DATE     DATE                     NOT NULL,
  LAST_UPDATED_BY      NUMBER                   NOT NULL
)

Schema for MOTDC_STG_SHIP_CONTAINER_TMP

CREATE TABLE MOTDC_STG_SHIP_CONTAINER_TMP
(
  TRANSACTION_ID       NUMBER,
  CONTAINER_TYPE       VARCHAR2(10 BYTE),
  CONTAINER_ID         VARCHAR2(40 BYTE),
  CONTAINER_LENGTH     NUMBER(9,3),
  CONTAINER_WIDTH      NUMBER(9,3),
  CONTAINER_HEIGHT     NUMBER(9,3),
  CONTAINER_WEIGHT     NUMBER(9,3)              NOT NULL,
  CONTAINER_PACK_TYPE  VARCHAR2(10 BYTE),
  CONTAINER_TRK_NUM    VARCHAR2(50 BYTE)        NOT NULL,
  CREATION_DATE        DATE                     NOT NULL,
  CREATED_BY           NUMBER                   NOT NULL,
  LAST_UPDATE_DATE     DATE                     NOT NULL,
  LAST_UPDATED_BY      NUMBER                   NOT NULL
)

Start Free Trial