Wanted: MySQL Magic Chanting

Here’s my problem. I’m working on integrating the EPA MOVES model, which means I’m living on MySQL. I need to load large amounts of data to the database, so I’m using the MySQL “LOAD DATA INFILE” command. The problem is that while the data file format is always the same, the tables that I need to load are going to differ, as are the file locations.

Complicating the issue is that I’m calling this from GAMS, which means any kind of advanced programming stunts (like parsing the file or dynamically generating SQL on the client) are pretty much out.

So I’d like to do something like this on the server:

LOAD DATA INFILE @data_file INTO TABLE @table_name

Except that MySQL really dislikes the idea of using variables to specify the table name.

“No problem,” I think to myself, “I’ll generate dynamic SQL on the server using PREPARE.” (Yes, I do think in hyperlinks.) A bit of cutesy abuse of CONCAT and PREPARE later, I’m ready to roll.

The catch? ERROR 1295: MySQL doesn’t support LOAD DATA in prepared statements. Nerts.

At this point, I’m seriously considering automatically generating a stored procedure load_data_TABLENAME for every table name in the database, and that’s making me feel sad deep inside. An alternative is to add a layer of indirection via a call to .Net, but the result will split some of the database logic which I’d like to keep consolidated on the server, not to mention complicating a system that I’d much prefer to simplify.

Any suggestions on how to make this work?

Comments

• April 12, 2010, James Moore wrote: Does loading into a temporary table and renaming help? The table rename might have different constraints than the load-data.
• April 13, 2010, Robert Fischer wrote: Maybe. Will that allow me to parameterize the table names? i.e., could I do something like this:

  load data infile @data_file into table tmp_foo;
  rename table tmp_foo to @new_table_name;
  

  I suspect not, but maybe...?

(Digital Fingerprint: bcecb67d74ab248f06f068724220e340 (68.142.243.103) )