the fastest way to insert multiple rows in mysql with a fixed value for a column

Question

I'm looking for the fastest way to insert multiple rows in a MySql table with two columns. One column has a constant value (1 for example) while the other always changes.

I am using php and i have a large string with email addresses (thousands), i want to put it in a table with the columns: email_cat_id and email_address. The email_cat_id is constant while the email_address change for every email.

Answer 1

It should be possible to insert multiple rows in one statement with something like this:

INSERT INTO [table] 
( [col1], [col2] ) VALUES 
( '[data for col1]', '[data for col2]' ), 
( '[data (2nd row) for col1]', '[data (2nd row) for col2]' ), 
...

Has the fixed column always the same value upon insertion? If so, you should set a default value for that column and omit its value in the insert statement.

If you need to insert A LOT of records, you should also check out the LOAD DATA INFILE command. This lets you bulk-import data from a text file, but it can be invoked like any other SQL command. (For more, see MySQL-documentation at http://dev.mysql.com/doc/refman/5.1/en/load-data.html)