#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0

    Update SQL table from a csv file


    I have a csv file that has a list of ID numbers like this (the second column in the csv file is not used):

    "111111111","5"
    "222222222","5"
    "333333333","7"

    I have a SQL database with a table that has a field called "photo", and also a field called "ID".

    If the csv file has a matching ID number, I would like to insert the ID number into the photo field. If there is no matching ID number in the csv file, then I want to insert a blank "" into the photo field for that record.

    So, how can I do this in SQL? I thank you in advance for your help.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    the first thing you have to do is load your csv into a table

    after that, it's a joined update, where you update your photos table from the table you've uploaded from the csv
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Can I get some details on how to do that? This would eventually have to be automated too, so it can run every night. Thanks.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    for details, i'd need your table layouts

    could you describe each table's column names please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    So the table that I am trying to update is called Students. It has a field called "ID", and a field called "Photo", and also many other fields such as birth date but they are not relevant here. One thing to note is there might sometimes be more than one record in the table with the same ID number, however they are the same student so the same value would be inserted into the Photo field for both IDs. There are never two different students with the same ID.

    The CSV file contains the student ID number, which was created by doing a directory listing of all photos we have on file.

    The ultimate purpose here is to update the photo field, so in our database we know we have a photo for those students.

    I would want to automate this to probably run every night. Thank you.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    Code:
    UPDATE students
       SET photo = 
           COALESCE(t2.id,'')
      FROM students t1
    LEFT OUTER
      JOIN csvtable t2
        ON t2.id = t1.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo