Search code examples
mysqlcsvdatesql-insertload-data-infile

Insert data from CSV in one column and custom input in other columns with LOAD DATA INFILE in MySQL?

I have a pre-defined table (with data) in a MySQL database with 3 columns:

brandId INT AUTO_INCREMENT,
brand CHAR,
insertDateTime DATETIME

And I have a list of brands stored in a csv file (10,000 rows).

I want to insert the brands into the table as new rows, with insertDateTime shows the date time of the insertion.

I know I can use LOAD DATA INFILE to load the brands from the csv, and I can use the NOW() function to compute insert datetime as we go, but how to combine them in one query?

Solution

  • You can use the SET clause of LOAD DATA to provide values that do not come from the input file.

    Consider the following syntax:

    LOAD DATA INFILE 'myfile.csv'
INTO TABLE mytable (brand)
SET insertDateTime = NOW();