Best way to mass-insert records?

Discussion in 'MySQL' started by subdivisions, May 10, 2010.

0
  1. #1
    Alright, I have a directory with about 500 .swf files. What would be the best say to give every file, without the file extension, a record in a table with each filename as a column value? I'd rather not do this all manually...

    Thanks
     
    subdivisions, May 10, 2010 IP
  2. Chocolate Lime

    Chocolate Lime Active Member

    Messages:
    399
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    58
    #2
    Use something like excel and paste the values in a column (Col A). Then, use a formula to generate your rows of insert statements in column (Col B) include comma separation.

    Forumla e.g.

    =CONCATENATE("(", A1, ")", ",")

    You can extend for additional columns.

    =CONCATENATE("(", A1, ",", B1, ")", ",")

    Fill down as for as you have rows to insert.

    Copy and paste the resultant sql into phpMyAdmin and execute.
     
    Chocolate Lime, May 13, 2010 IP
  3. joebert

    joebert Well-Known Member

    Messages:
    2,150
    Likes Received:
    88
    Best Answers:
    0
    Trophy Points:
    145
    #3
    Use the following command to generate a list of SWF files.

    ls *.swf > list.csv
    Code (markup):
    Then use the IMPORT tab of phpMyAdmin to import it. Select CSV for the format of the file. The default options should work.

    Once they're imported, use the following SQL statement to trim the extension from the end of each row all in one go, assuming your column name is "filename".

    UPDATE table SET filename = REPLACE(filename, '.swf', '');
    Code (markup):
     
    joebert, May 18, 2010 IP