0

I populated my target table using an ETL application as per our business requirements. This target table has 5 "levels" and for each of these levels I have to insert their corresponding "child" data. Here is my script to select the the "child" data to insert (mind you there are other columns but the below script is just to show how i iterate through the table to identify the 5 leves:

select
code_test_data + 200 as CODE_test_data_child,
test_data from targetTable
where level_test_data in (select level from dual t connect by level <=5)
order by level_test_data

the aim is to have the sql script execute to insert the child data after my etl application inserts the primary data.

My question is:

How will the id column of the target table be managed for the "child" inserts? the id column for the primary insert is managed by my etl application. I am thinking of several approaches:

1) create a function that will return the max(id)+1. So for each insert, I can call this function to add the id

2) Sequence: use Oracle's sequence functionality to add the max id. But I would have to "hard" code the starting value of the sequence. The starting value will come from the target table's max(id). I dont really want to hard-code the starting value

3) I tried using the max(id)+1 in my insert into (col1,col2..) select max(id)+1.. but that just incremented the id of each row by 1.

Would anyone have some better solutions to share please?

thanks heaps

EDIT:

After inserting primary data, I will insert child data but the ids of the child inserts must be max(id)+1: Example: row1=1, primaryTest1, level1... row2= 2, primaryTest2, Level2 ...newinserts=3, childTest1 | 4, childTest1 | 5, childTest2 | 6, childTest2 ...I hope this example clarifies my question.

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
Esperanza
  • 115
  • 10
  • Which Oracle Version? – Kaushik Nayak Oct 18 '17 at 05:44
  • Possible duplicate of [How to create id with AUTO\_INCREMENT on Oracle?](https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle) – Kaushik Nayak Oct 18 '17 at 05:47
  • I'm not sure what you're asking. My best interpretation is - you want to insert data (1 primary insert, 5 child inserts) into a single table. The PK of the primary is pre-determined by the ETL application. You want to assign PK values to the child inserts without clashing sequence values. Is that right? If it is I suggest you use a composite PK using two columns - level (0 for primary insert, otherwise 1-5) and then a key (using ETL value for primary and a sequence for the child inserts). It then does not matter if there are clashes in the 'key' column – Christian Palmer Oct 18 '17 at 07:30
  • Hi Christian, after inserting primary data, I will insert child data but the ids of the child inserts must be max(id)+1: **Example: row1=1, primaryTest1, level1... row2= 2, primaryTest2, Level2 ...newinserts=3, childTest1 | 4, childTest1 | 5, childTest2 | 6, childTest2** ...I hope this example clarifies my question. Thanks – Esperanza Oct 18 '17 at 08:26

1 Answers1

0

I found a solution to my question. This post helped me

Here is my solution (if it can help someone else):

1) Create my sequence using Start value from max(id) of my targetTable. poste link

2) Create a procedure that calls this sequence to insert my id, including child values in my select statement: 

select
seq_name.nextval,
code_test_data + 200 as CODE_test_data_child,
test_data from targetTable
where level_test_data in (select level from dual t connect by level <=5)
order by level_test_data

Thank you

Esperanza
  • 115
  • 10