[R] RMySQL - Bulk loading data and creating FK links

[Olga Lyashevska]

Hi Nathan,

> I have a table (contact) with several fields and it's PK is an auto  
> increment field. I'm bulk loading data to this table from files  
> which if successful will be about 3.5million rows (approx 16000 rows  
> per file). However, I have a linking table (an_contact) to resolve a  
> m:m relationship between the an and contact tables. How can I  
> retrieve the PK's for the data bulk loaded into contact so I can  
> insert the relevant data into an_contact.
>
> I currently load the data into contact using: dbWriteTable(con,  
> "contact", dat, append=TRUE, row.names=FALSE)
>
> But I then need to get all the PK's which this dbWriteTable()  
> appended to the contact table so I can load the data into my  
> an_contact link table. I don't want to issue a separate INSERT query  
> for each row in dat and then use MySQLs LAST_INSERT_ID()  
> function....not when I have 3.5million rows to insert!
>
> Any pointers welcome,
> Nathan
>
It looks to me more like sql question. Why don't you use sql to write  
a query which will join all tables for you and then use RMySQL to  
retrieve relevant data?
Of course,  you could also dbGetQuery.

Can you please explain a bit how you entity-relationship diagram looks  
like?


Cheers,
Olga