批量插入文件路径作为存储过程参数 [英] Bulk insert file path as stored procedure parameter

问题描述

我正在尝试创建一个存储过程，以从 CSV 导入。如果我具有硬编码的文件路径，则一切正常，但是我想将文件路径作为参数。当我尝试SQL Sever Management Studio时会生成错误：

I'm trying to create a stored procedure to import from CSV. Everything works if I have a hard coded file path, but I want to take a file path as a parameter. When I try SQL Sever Management Studio generates an error:

'@ filePath'附近的语法不正确。

Incorrect syntax near '@filePath'.

（实际上，如果我输入的内容不是纯字符串（例如'C：'+'/ dir'）这会导致错误。）

(In fact, if I put anything but a pure string(eg. 'C:'+'/dir') it gives an error.)

这是我代码的简化版本：

This is a simplified version of my code:

Create procedure [importFile](@filePath varchar(Max))
AS
BEGIN
    create table #Temp
    (
      row1 int,
      row2 varchar(5),
      row3 bit
    )
    BULK insert
      #Temp
      from @filePath
      With(
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n'
      )
    ...
END

任何解释吗？

推荐答案

使用动态SQL将文件名变量注入到一个带有批量插入语句的字符串，并使用 sp_executesql 执行它。您可能需要添加一些错误检查，以检查路径是否有效，等等。

Use dynamic SQL to inject the file name variable into a string with the bulk insert statement and the use sp_executesqlto execute it. You might want to add some error checking to check that the path is valid and so on.

CREATE PROCEDURE [importFile] (@filePath VARCHAR(MAX))
AS
BEGIN
    CREATE TABLE #Temp
    (
      row1 int,
      row2 varchar(5),
      row3 bit
    )

    DECLARE @SQL NVARCHAR(MAX) = ''
    SET @SQL = N'
    BULK INSERT #Temp
      FROM ''' + @filePath + '''
      WITH (
        FIELDTERMINATOR = '','',
        ROWTERMINATOR = ''\n''
      )'

     -- ...

     EXEC sp_executesql @SQL
END

-- to run it:
EXEC importFile 'd:\test.csv'

这篇关于批量插入文件路径作为存储过程参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！