SQL Server 2012：基于主键从CSV更新数据

Question

所以我的数据库里有这个表。我们将其称为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

我有这个CSV文件：

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

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

Answer 1

可以使用openrowset访问SQL Server中的csv文件。此示例假定表和csv文件中有匹配的列名。您可以根据需要调整文件名、目录和列名：

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