如何将本地CSV文件导入远程服务器上的SQL Server数据库

报错根因 #

你遇到的报错确实为权限问题：SQL Server 执行OPENROWSET时使用的是SQL Server服务进程的运行账户权限，而非你当前操作SSMS的Windows用户权限。你D盘的test文件夹默认给了服务账户访问权限，但是用户桌面路径属于个人私有目录，默认只有对应的Windows用户才有访问权限，因此会触发数据源初始化失败的报错。

可封装为存储过程的导入方案 #

以下方案均支持直接通过SQL语句实现，可直接封装进存储过程满足批量、反复执行的需求：

• 方案1：给SQL Server服务账户开放本地路径权限 #

  1. 打开Windows服务控制台（运行输入services.msc），找到对应SQL Server 2019实例的服务，查看「登录为」列的运行账户，默认可能为NT SERVICE\MSSQLSERVER、网络服务或自定义服务账户。
  2. 右键存放CSV的文件夹（此处为C:\Users\batman\Desktop），依次选择「属性」-「安全」-「编辑」-「添加」，将上一步查到的SQL Server服务账户添加到权限列表，授予「读取和执行」「列出文件夹内容」「读取」权限即可。
  3. 权限配置完成后，你原来的OPENROWSET语句无需修改即可正常运行。

• 方案2：使用网络共享UNC路径访问 #

  1. 将本地存放CSV的文件夹设置为共享文件夹，给SQL Server服务运行账户同时开放共享读取权限和本地NTFS读取权限。
  2. 直接用共享UNC路径编写查询语句即可，示例如下：

  SELECT * 
  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                  'Text;Database=\\你的本地计算机名\CSV共享文件夹名\', 'SELECT * FROM test.csv')
  

  该方案适合固定在某台本地机器执行导入的场景，无需每次手动复制文件到SQL Server服务器本地磁盘。

• 方案3：改用BULK INSERT实现导入 #

  如果你不想依赖Microsoft.ACE.OLEDB驱动，可改用BULK INSERT语法，逻辑更简洁，同样支持UNC路径：

  BULK INSERT 你的目标表名
  FROM '\\你的本地计算机名\CSV共享文件夹名\test.csv'
  WITH (
      FORMAT = 'CSV',
      FIRSTROW = 2, -- CSV第一行为表头则设为2，无表头则设为1
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      CODEPAGE = '65001' -- 如果CSV为UTF-8编码可添加该参数避免乱码
  )
  

• 方案4：使用OPENROWSET的BULK选项 #

  该方案无需提前创建目标表结构，可直接查询CSV内容后做二次处理，灵活性更高：

  SELECT * FROM OPENROWSET(
     BULK '\\你的本地计算机名\CSV共享文件夹名\test.csv',
     FORMAT = 'CSV',
     FIRSTROW = 2,
     CODEPAGE = '65001'
  ) AS csv_data
  

注意：如果你的CSV使用非逗号的自定义分隔符、存在包含分隔符的文本字段，可调整WITH子句内的参数匹配你的文件格式

内容的提问来源于stack exchange，提问作者Bob