I was looking for quite some time on whats the best way of selecting or inserting some data.
I have two tables:
ReportData: report_data_id(pk), source_id(fk), value1;
Source: source_id(pk), source_name(varchar250);
I am doing a bulk insert into the ReportData around 10k of rows at once, but I don't know which is the best way of getting the source_id, because not all of the sources are defined.
I was thinking that a good way is writing a function to which I send a source (which is a string) and if the source exist return the id, if not insert the source and then return the id. And then before the bulk insert I will add this into the VALUES SELECT * FROM get_source('SourceString'). This then on insert is going to query that function 10k times.
In my ReportData I have like 10 columns which are FK and on each I need to do the same thing as described above.
Is this a good way of doing what I described above?
