CODEWITHSUNDEEP
sqlmysql
Shiv Deepak
Shiv Deepak

Reputation: 3116

MySQL bulk value insert

I have a bunch of emails, each separated by a comma (,) (not csv). I want to upload them to a database table (with single field email) such that each email goes into separate record entry. what could be the most easiest way to do that? I have an idea of using grep to replace commas with my sql syntax.. but searching for any other workaround.. any idea?

Upvotes: 2

Views: 361

Answers (4)

Jeremy
Jeremy

Reputation: 122

Convert the current ',' separated email list to a one line per email list

tr ',' '\n' < inputfilename > outputfilename

use load data infile after logging into mysql, make sure your table only has one column in this case

load data infile 'outputfilename' into table tablename;

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Upvotes: 1

Marc B
Marc B

Reputation: 360572

Perhaps something like:

LOAD DATA INFILE '/where/the/file/is'
INTO TABLE table (email)
FIELDS TERMINATED BY ','
LINES STARTING BY '';

Syntax docs here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Upvotes: 2

Peter Bailey
Peter Bailey

Reputation: 105868

MySQL supports multiple inserts in a single statment

INSERT INTO [Table] ([col1], [col2], ... [colN] )
VALUES ([value1], [value2], ... [valueN] )
     , ([value1], [value2], ... [valueN] )
     , ([value1], [value2], ... [valueN] )
;

You could pretty quickly format a comma-separated file into this format.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562250

I'd use shell tools like sed or awk to convert the input format to something that mysqlimport can handle.

Upvotes: 1

Related Questions