locked
Update SQL table from a csv file RRS feed

  • Question

  • I have a csv file that has a list of ID numbers like this:

    "111111111"
    "222222222"
    "333333333"

    I have a SQL database with a table that has a text 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.

    Saturday, August 25, 2012 4:50 AM

Answers

  • Follow these steps,

    • Read the csv file and load it to table, visit this link for help :http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
    • Based on the id perform update
    • Check this query

    update t

    set t.photo=case when s.photo is null then "" else s.photo end as photo

    from target t left outer join temp_table s

            on t.id=s.id

    Regards,

    YB

    • Proposed as answer by ank hit Sunday, August 26, 2012 12:25 AM
    • Marked as answer by Iric Wen Thursday, September 6, 2012 8:24 AM
    Saturday, August 25, 2012 6:24 AM
  • Hi Frnd,

    First you need to load your csv file data into sql table.

    Assuming that you loaded csv file data into #csvFile table. now the required query will be :

    create table #csvFile

    (

    ID int

    )

    insert into #csvFile values (111111111);

    insert into #csvFile values (222222222);

    insert into #csvFile values (333333333);

    Create table #dbTable

    (

    ID int,   

    Photo text

    )

    insert into #dbTable values (111111111, '');

    insert into #dbTable values (522890222, '');

    insert into #dbTable values (222222222, '');

    insert into #dbTable values (111111234, '');

    insert into #dbTable values (200022222, '');

    insert into #dbTable values (333333333, '');

    Update #dbTable

    Set Photo = convert(nvarchar,#dbTable.ID)

    where #dbTable.ID in (Select DT.ID

                                  from #dbTable DT inner join #csvFile CF

                                  on DT.ID = CF.ID

                                  )

    UPDATE #dbTable

    set #dbTable.Photo = 'RahulVairagi'

    where #dbTable.ID not in (Select DT.ID

                                  from #dbTable DT inner join #csvFile CF

                                  on DT.ID = CF.ID

                                  )

                           

    Select * from #csvFile

    Select * from #dbTable

    Drop table #csvFile

    Drop Table #dbTable


    Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

    • Proposed as answer by ank hit Sunday, August 26, 2012 12:25 AM
    • Marked as answer by Iric Wen Thursday, September 6, 2012 8:24 AM
    Saturday, August 25, 2012 11:09 AM

All replies

  • Follow these steps,

    • Read the csv file and load it to table, visit this link for help :http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
    • Based on the id perform update
    • Check this query

    update t

    set t.photo=case when s.photo is null then "" else s.photo end as photo

    from target t left outer join temp_table s

            on t.id=s.id

    Regards,

    YB

    • Proposed as answer by ank hit Sunday, August 26, 2012 12:25 AM
    • Marked as answer by Iric Wen Thursday, September 6, 2012 8:24 AM
    Saturday, August 25, 2012 6:24 AM
  • Hi Frnd,

    First you need to load your csv file data into sql table.

    Assuming that you loaded csv file data into #csvFile table. now the required query will be :

    create table #csvFile

    (

    ID int

    )

    insert into #csvFile values (111111111);

    insert into #csvFile values (222222222);

    insert into #csvFile values (333333333);

    Create table #dbTable

    (

    ID int,   

    Photo text

    )

    insert into #dbTable values (111111111, '');

    insert into #dbTable values (522890222, '');

    insert into #dbTable values (222222222, '');

    insert into #dbTable values (111111234, '');

    insert into #dbTable values (200022222, '');

    insert into #dbTable values (333333333, '');

    Update #dbTable

    Set Photo = convert(nvarchar,#dbTable.ID)

    where #dbTable.ID in (Select DT.ID

                                  from #dbTable DT inner join #csvFile CF

                                  on DT.ID = CF.ID

                                  )

    UPDATE #dbTable

    set #dbTable.Photo = 'RahulVairagi'

    where #dbTable.ID not in (Select DT.ID

                                  from #dbTable DT inner join #csvFile CF

                                  on DT.ID = CF.ID

                                  )

                           

    Select * from #csvFile

    Select * from #dbTable

    Drop table #csvFile

    Drop Table #dbTable


    Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

    • Proposed as answer by ank hit Sunday, August 26, 2012 12:25 AM
    • Marked as answer by Iric Wen Thursday, September 6, 2012 8:24 AM
    Saturday, August 25, 2012 11:09 AM