SQL Server 2012: update data from CSV based on primary keys

Question

So I have this table in my database. We'll say it's called users.

UserID   UserName   UserDescription
-----------------------------------
1        test1      Test User #1
2        test2      Test User #2
3        test3      Test User #3
4        test4      Test User #4
5        test5      Test User #5

And I have this CSV file:

1,"Harry Potter"
4,"Hermione Granger"
3,"Ron Weasley"

What I want is to update the table from the CSV file. Here's the result I'm looking for:

UserID   UserName   UserDescription
-----------------------------------
1        test1      Harry Potter
2        test2      Test User #2
3        test3      Ron Weasley
4        test4      Hermione Granger
5        test5      Test User #5

Without having to write a program to do this, is there a way to accomplish this kind of update from the CSV file?

Basically I want to do something like what you can do in the Import Data wizard - map the CSV columns to their respective database columns - but to have the database update any columns where the primary key in the database matches the assigned column in the CSV file.

(SQL Server 2012)

I'm a fan of `OPENROWSET` (see @ron-smith's anwer). I prefer it to importing data to a temp table. If anyone is interested: [Getting Started With OPENROWSET and the BULK Rowset Provider](http://itsalljustelectrons.blogspot.com/2016/01/Openrowset-Bulk-Rowset-Provider-Part-2.html) — Dave Mason, Dec 01 '16 at 18:13

score 2 · Accepted Answer · edited May 23 '17 at 10:30

2

I would suggest the following steps:

Import the CSV into a temporary table.

Related question for details: Import CSV file into SQL Server.
Update the main table with the data from the temporary table.

Related question for details: How do I UPDATE from a SELECT in SQL Server?

Code example:
```
UPDATE users
   SET users.UserDescription = #import.UserDescription
  FROM users 
       INNER JOIN #import ON users.UserID = #import.UserID
```

edited May 23 '17 at 10:30

Community

1
1

answered Dec 01 '16 at 17:13

Heinzi

167,459
57
363
519

Didn't think of this, turns out to be the simplest way to do it. Thanks! – fdmillion Dec 01 '16 at 17:24

score 2 · Answer 2 · answered Dec 01 '16 at 17:25

You can use openrowset to access your csv file in SQL Server. This example assumes you have matching column names in your table and csv file. You can adjust file name, directory, and column names as necessary:

update users
    set users.UserDescription = csv.UserDescription
    from users
        inner join openrowset('MSDASQL',
            'Driver={Microsoft Access Text Driver (*.txt, *.csv)}',
            'select * from C:\YourCSV.csv') as csv
            on csv.UserID = users.UserID

Doesn't this require that the file resides on the SQL server? Assume I don't have write access to put arbitrary CSV files on the server that runs SQL Server. — fdmillion, Dec 01 '16 at 20:11

SQL Server 2012: update data from CSV based on primary keys

2 Answers2