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

Nathan S. Watson-Haigh nathan.watson-haigh at csiro.au
Wed Jan 27 07:32:59 CET 2010


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

-- 
--------------------------------------------------------
Dr. Nathan S. Watson-Haigh
OCE Post Doctoral Fellow
CSIRO Livestock Industries
University Drive
Townsville, QLD 4810
Australia

Tel: +61 (0)7 4753 8548
Fax: +61 (0)7 4753 8600
Web: http://www.csiro.au/people/Nathan.Watson-Haigh.html



More information about the R-help mailing list