MySQL: SQL to insert rows using a string of ids

Question

I need to insert ~150 simple rows (an id, and a static status of 'discard'). I have a string of the ids:

'123', '234r', '345', '456xyz'...

What's the simplest way to insert rows using this string of ids?

It seems like maybe there's some way to split the string on commas and... create a temp table to ...? I don't know - it just seems like this is the kind of thing that MySQL often manages to pull off in some cool, expedient way.

Use an editor with search and replace to get the values lists for this insert. I assume this is a one time job. — VMai, Jul 30 '14 at 14:28
@Aleatoric: I haven't tried anything yet. The only solid idea I have is the same as the one from `monitor`. I'm just pushing this off & fishing for better ideas. — doub1ejack, Jul 30 '14 at 15:25
@VMai: Well, I've gotten two such emails today and will probably get another 10-15 before this project wraps up. — doub1ejack, Jul 30 '14 at 15:30
CSV would be a fine idea. And it's really something you should know how to do if you plan on working with databases. This is something pretty simple that you should have done some research into. How to insert bulk data into a databases is a core skill. Furthermore, Stack Overflow expects you to have at least tried to solve the problem before asking a question. — WinterMute, Jul 30 '14 at 15:30
You would probably be best served with either [mysqlimport](http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html) or [LOAD DATA INFILE](http://dev.mysql.com/doc/refman/5.0/en/load-data.html) — WinterMute, Jul 30 '14 at 15:33
@doub1ejack Two ideas: a) a simple search `,` and replace with linefeed, so you've got a CSV file and import this with LOAD DATA INFILE. b) a simple script that converts these strings in just one INSERT statement with multiple VALUES. — VMai, Jul 30 '14 at 15:34
@VMai: your option b) works well for me - a little text editing that gives me a single sql statement to run. If you make it an answer, I'll select it. — doub1ejack, Jul 30 '14 at 16:48

score 1 · Accepted Answer · answered Jul 30 '14 at 17:13

An example how to do create an INSERT statement with a few lines of PHP:

<?php
    // copy your string of ids into this variable
    $input = "'123', '234r', '345', '456xyz'";
    // modify next line to get your desired filename
    $filename = 'insert.sql'
    // modify next line to your table name
    $insert_statement = "INSERT INTO your_table_name (id, status) VALUES \n" .
         '(' . implode(", 'discard')\n(", explode(', ', $input)) . ", 'discard');\n"; 
    file_put_contents($filename, $insert_statement);
?>

Note

This is for this special use case. If the string of ids contains some special characters like single quotes, then this simple approach will fail.

Thanks. I'm not actually doing this in code - i was trying to get as close to pure SQL as possible. However, by using a [find/replace regex in a text editor](http://screencast.com/t/izMS3tato) I can build [the same SQL statement you're using](http://screencast.com/t/tZkTectzt2). — doub1ejack, Jul 30 '14 at 17:33

score 0 · Answer 2 · answered Jul 30 '14 at 14:34

0

The one way is to create CSV file with appropriate records and upload it at once to mysql. Please follow this tutorial: http://www.mysqltutorial.org/import-csv-file-mysql-table/

answered Jul 30 '14 at 14:34

monitor

251
2
9

MySQL: SQL to insert rows using a string of ids

2 Answers2