IKM Oracle Multi Table Insert

IKM Oracle Multi Table Insert

1. Integrates data from one source into one to many Oracle target tables in append mode
2. This IKM must be used in integration interfaces that are sequenced in a Package
3. The first interface of the Package must have a temporary target and the KM option DEFINE_QUERY set to YES.
4. This first interface defines the structure of the SELECT clause of the multi-table insert statement (that is the source flow).
5. Subsequent integration interfaces must source from this temporary data store and have the KM option IS_TARGET_TABLE set to YES.
6. The last interface of the Package must have the KM option EXECUTE set to YES in order to run the multi-table insert statement.

STEP 1 : Creating and Reverse Engineering Source

1. We can use EMP table of SCOTT schema as source
2. Reverse engineer EMP table into SOURCE  MODEL

STEP 2 : Creating and Reverse Engineering Target

1. Create Four target tables in Target Schema using below script

Create table Multi_Table_Insert_TEMP_EMP as select * from scott.emp where 1=2; Create table Multi_Table_Insert_EMP10 as select * from scott.emp where 1=2; Create table Multi_Table_Insert_EMP20 as select * from scott.emp where 1=2; Create table Multi_Table_Insert_EMP30 as select * from scott.emp where 1=2;

2. Reverse engineer into TARGET MODEL

STEP 3 : Creating First Interface

1. Create interface with the name of INT_Multi_Table_Insert_TEMP_EMP
2. Click on mapping tab → Drag and drop source (EMP)and target (Multi_Table_Insert_TEMP_EMP) in respective locations
3. Click on flow tab
4. Select IKM as IKM Oracle Multi Table Insert
5. Select Define_Query = True
6. Select IS_TARGET_TABLE= False

STEP 4 : Creating Second Interface

1. Create interface with the name of INT_Multi_Table_Insert_EMP10
2. Click on mapping tab → Drag and drop source (EMP)and target (Multi_Table_Insert_EMP10) in respective locations
3. Click on flow tab
4. Select IKM as IKM Oracle Multi Table Insert
5. Select Define_Query = False
6. Select IS_TARGET_TABLE= True

STEP 5 : Creating Third Interface

1. Create interface with the name of INT_Multi_Table_Insert_EMP20
2. Click on mapping tab → Drag and drop source (EMP)and target (Multi_Table_Insert_EMP20) in respective locations
3. Click on flow tab
4. Select IKM as IKM Oracle Multi Table Insert
5. Select Define_Query = False
6. Select IS_TARGET_TABLE= True

STEP 6 : Creating Fourth Interface

1. Create interface with the name of INT_Multi_Table_Insert_EMP30
2. Click on mapping tab → Drag and drop source (EMP)and target (Multi_Table_Insert_EMP30) in respective locations
3. Click on flow tab
4. Select IKM as IKM Oracle Multi Table Insert
5. Select Define_Query = False
6. Select IS_TARGET_TABLE= True
7. Select Execute = True

STEP 7 : Create Package

1. Create package with the name of pkg_Multi_Table_Insert
2. Click on diagram tab → drag and drop below interfaces and  connect as shown
1. INT_Multi_Table_Insert_TEMP_EMP
2. INT_Multi_Table_Insert_EMP10
3. INT_Multi_Table_Insert_EMP20
4. INT_Multi_Table_Insert_EMP30

3. Run the package → observe output in three tables