Assume this is my Destination table:
Name | Reason for Visit | Visit Number | Visit_date
Dan | Foo | 1 | 04/03/2014
Henry| Bar | 1 | 05/07/2014
Dan | Tau | 2 | 08/10/2014
I already have the Name, Reason_For_Visit and Visit_Date in my SOURCE table, and have to create the Visit_Number column. Am I allowed to sub-query the still-forming destination table like this and will I get the correct visit_number?
insert into DESTINATION
NAME,
REASON_FOR_VISIT,
(select count(d.Name)+1 from destination d where d.Name=Name) AS VISIT_NUMBER,
VISIT_DATE
from SOURCE
ORDER BY VISIT_DATE ASC;
If rows are inserted one by one, I feel like I should get the correct Visit_number.
The answer to your question is YES - if you insert the first row, the second insert will see the first row already in the destination table.
Still, doing it row by row is an inefficient way to accomplish what you are doing. Why don't you try out something like this:
insert into destination
select
name,
reason_for_visit,
row_number() over (partition by name order by visit_date) as visit_number,
visit_date
from source
The row_number() function will compute the visit_number correctly for you, and inserting all the rows with one query will definitely be more efficient.
Good luck!
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments