Question about Inserting Records into a table in Oracle
I have csv file, which I am loading through SQL Loader for a table.
my_table data is as follows:
emp_no line some other columns
100 1
100 5
100 3
101 1
101 4
101 3
emp_no & line both constitute the primary key.
as you can see, some of the lines for the emp_no are missing. So, I am asked to sequence the line numbers by ordering them according to emp_no & line.
So, after inserting through SQL Loader I am calling a function which updates the rows & returns the number of rows updated. Please find the Function attached.
With this function(which I got from e-e), I will get the result as follows:
emp_no line some other columns
100 1
100 2
100 3
101 1
101 2
101 3
But the problem here is
The update statement is updating all the rows inside the table. If there are 10 million rows, it is updating all the rows. It is taking a lot of time.
I only want to update the newly inserted rows. So, if i insert two rows, it should update only those two rows. Is there any way to do that?
my_table data is as follows:
emp_no line some other columns
100 1
100 5
100 3
101 1
101 4
101 3
emp_no & line both constitute the primary key.
as you can see, some of the lines for the emp_no are missing. So, I am asked to sequence the line numbers by ordering them according to emp_no & line.
So, after inserting through SQL Loader I am calling a function which updates the rows & returns the number of rows updated. Please find the Function attached.
With this function(which I got from e-e), I will get the result as follows:
emp_no line some other columns
100 1
100 2
100 3
101 1
101 2
101 3
But the problem here is
The update statement is updating all the rows inside the table. If there are 10 million rows, it is updating all the rows. It is taking a lot of time.
I only want to update the newly inserted rows. So, if i insert two rows, it should update only those two rows. Is there any way to do that?
create or replace FUNCTION Proc(schema_name in VARCHAR)
RETURN NUMBER
IS
SQL_TXT VARCHAR(700);
BEGIN
SQL_TXT:= 'UPDATE '||schema_name||'.my_table x set ( line ) = ( select y.val from (select emp_no, line, row_number() over (partition by emp_no order by line ) as val from '||schema_name||'.my_table ) y where x.emp_no = y.emp_no and x.line = y.line)
EXECUTE IMMEDIATE SQL_TXT;
RETURN SQL%ROWCOUNT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
RETURN -1;
END;
ASKER
There is also one more problem,
Suppose there are 1 million records in the table. And if I insert some records into table_B using the SQL Loader without the line no.
If some records violate the primary key, How would SQL Loader while inserting into table_B know that some records are violating the primary key?
Creating a trigger:
First I have to order the lines and then i have to assign the line numbers.
like if there are records like:
101 1
101 4
101 3
then first it needs to be order according to the line
101 1
101 3
101 4
then I have to assign the row_number to it.
101 1
101 2
101 3
How is this possible with the trigger?
Suppose there are 1 million records in the table. And if I insert some records into table_B using the SQL Loader without the line no.
If some records violate the primary key, How would SQL Loader while inserting into table_B know that some records are violating the primary key?
Creating a trigger:
First I have to order the lines and then i have to assign the line numbers.
like if there are records like:
101 1
101 4
101 3
then first it needs to be order according to the line
101 1
101 3
101 4
then I have to assign the row_number to it.
101 1
101 2
101 3
How is this possible with the trigger?
I have not used SQL Loader in years and do not have documentation available, but I am highly confident about the concept I am about to propose. The idea is to insert the rows with the correct line number to begin with. I do not know the origin of the line number with gaps, but certainly you can derive a sequential next line number within emp_no during the SQL Loader insert.
For the input specification, use something like this:
line EXPRESSION "Select max(line) + 1 from YourTable where emp_no = :emp_no_field"
For the input specification, use something like this:
line EXPRESSION "Select max(line) + 1 from YourTable where emp_no = :emp_no_field"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sdstuber:
I like your idea, I so badly want to do this way. But, what if some of the records inserted in the temporary table are duplicates? insert statement would end abruptly in the middle causing other records not to be inserted right?
I like your idea, I so badly want to do this way. But, what if some of the records inserted in the temporary table are duplicates? insert statement would end abruptly in the middle causing other records not to be inserted right?
ASKER
@dgmg:
It's not sequential for line numbers. I have to order them first according to that emp_no & then insert them into the table. I got that idea too first but they told me that is not the case. That's why i had to suffer a lot.
It's not sequential for line numbers. I have to order them first according to that emp_no & then insert them into the table. I got that idea too first but they told me that is not the case. That's why i had to suffer a lot.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@sdstuber
I want to insert only one.
How can i do that inside this loop? How can I discard the duplicates? Please suggest.
declare
emp_no temp.emp_no%type;
cursor temp_cur is select distinct(emp_no) from temp;
begin
open temp_cur;
loop
fetch temp_cur into emp_no;
exit when temp_cur%notfound;
select max(line) into v_line from my_table
where emp_no = emp_no;
insert into my_table (emp_no, line, other_columns)
(select emp_no, v_line + row_number() over(partition by emp_no order by line), other_columns
from temp where emp_no = emp_no)
end loop;
close temp_cur;
end;
/
I want to insert only one.
How can i do that inside this loop? How can I discard the duplicates? Please suggest.
declare
emp_no temp.emp_no%type;
cursor temp_cur is select distinct(emp_no) from temp;
begin
open temp_cur;
loop
fetch temp_cur into emp_no;
exit when temp_cur%notfound;
select max(line) into v_line from my_table
where emp_no = emp_no;
insert into my_table (emp_no, line, other_columns)
(select emp_no, v_line + row_number() over(partition by emp_no order by line), other_columns
from temp where emp_no = emp_no)
end loop;
close temp_cur;
end;
/
ASKER
@sdstuber
I am sorry, I was hasty.
delete from temp t1 where (t1.no,t1.name) in (select no, name from my_table);
Should i delete as above before inserting like above?
I get it now. How can i display the status then if it is taking long time?
I am sorry, I was hasty.
delete from temp t1 where (t1.no,t1.name) in (select no, name from my_table);
Should i delete as above before inserting like above?
I get it now. How can i display the status then if it is taking long time?
sure that should work
as for display you have several options.
you can use dbms_output, but that will only be visible when the process completes.
You can write to a log file or write to a log table (use autonomous transactions for the log table)
you can use dbms_application_info to fill in the action/module/client_info portions of v$session.
as for display you have several options.
you can use dbms_output, but that will only be visible when the process completes.
You can write to a log file or write to a log table (use autonomous transactions for the log table)
you can use dbms_application_info to fill in the action/module/client_info portions of v$session.
utl_tcp or dbms_pipe could be used to send messages to a separate process also
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry i didn't see that. I thought it is for the entire file.
line EXPRESSION "Select max(line) + 1 from YourTable where emp_no = :emp_no_field "
However, Since sequencing is not an option for me. I have to order first which I think it is not possible with SQL Loader.
First I insert into the temp table with SQLLoader. Then, Can I do like this?
Instead of looping over each emp_no can I use like this inside a function which takes schema_name as parameter?
INSERT_QUERY:='insert into '||schema_name||'.my_table
EXECUTE IMMEDIATE INSERT_QUERY;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@sdstuber
Thank you so much. One last question,
Do i have to use like:
insert into mytable(all the columns) select columns from mytemp;
or can i use like (Can i ignore saying the columns of mytable? because mytemp is the replica of the mytable and will have same number of columns as the mytable)
insert into mytable select columns from mytemp;
Because, there are around 100 columns in the mytable besides emp_no & line.
Thank you so much. One last question,
Do i have to use like:
insert into mytable(all the columns) select columns from mytemp;
or can i use like (Can i ignore saying the columns of mytable? because mytemp is the replica of the mytable and will have same number of columns as the mytable)
insert into mytable select columns from mytemp;
Because, there are around 100 columns in the mytable besides emp_no & line.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much for the answer and your valuable time
glad I could help
or if you want to insert 10 million records, you can insert the whole 10 M records into a table without a line_no, and use create table table_B as select * from table_a statement to compute line_no on the fly. after this drop table_A and keep table_B