The problem

We have 81 products in a database without images. We need to upload these images as a batch and get the products to point to the images.

The solution

First we have to do a bit of snooping at the database level. We discover that Drupal maintains a table called 'files' containing our files and file paths. This implementation contains a CCK table called 'content_field_image_cache' linking the nodes to the image files. Files are stored in /sites/default/files. Nice.

Now that we have an idea of what's going on we can FTP the files to the server where Drupal would expect them. Then we create entries in the 'files' table to match. MySQL cannot generate number sequences easily but lucky for us we have images named by 'node.nid' so we can use the node table to generate our sequence like so:

 SELECT 1 uid, CONCAT(nid, '.jpg') filename,  
CONCAT('sites/default/files/', CONCAT(nid, '.jpg') filepath, 'image/jpeg' filemime, 1 status 
FROM node 
WHERE nid >= 240 AND nid <= 332 

You can see this is very specific to my situation. Change the parameters to suit your needs. Once you are satisfied that it looks ok you can insert it into the table like this:

INSERT into files(uid,filename, filepath, filemime, status) SELECT 1 uid, CONCAT(nid, '.jpg') filename, CONCAT('sites/default/files/', CONCAT(nid, '.jpg'))  filepath, 'image/jpeg' filemime, 1 status FROM node WHERE nid >= 240 AND nid <= 332  

Now that the files table is populated we need to get the 'content_field_image_cache' table populated. Use this query:

 INSERT INTO content_field_image_cache( vid, nid, delta, field_image_cache_fid, field_image_cache_list, field_image_cache_data ) 
SELECT nid vid, nid nid, 0 delta, (
SELECT fid
FROM files
WHERE filename = CONCAT( nid, '.jpg' )
) field_image_cache_fid, 1 field_image_cache_list, 'a:3:{s:11:"description";s:0:"";s:3:"alt";s:0:"";s:5:"title";s:0:"";}' field_image_cache_data
FROM node
WHERE nid >=240 AND nid <=332