在存储过程中使用动态SQL进行大容量插入

Question

我有一个.NET应用程序，从中调用2个存储过程。其中一个存储过程是大容量插入，我将从.NET应用程序向其传递适当的文件名、表名和分隔符。经过一些研究，我发现我需要使用动态SQL来允许BULK INSERT中的可变文件名。

我现在的代码是：

CREATE PROCEDURE transfer_data 
@file_path VARCHAR, @t_name VARCHAR, @delimeter VARCHAR
AS
BULK INSERT @t_name
FROM @file_path
WITH (
FIELDTERMINATOR = @delimeter,
ROWTERMINATOR = 'n'
);

我该如何重构它？我看到的其他示例(带有变量文件名的BULK INSERT(仍在设置查询中的变量，但我将从.NET应用程序传递参数。

Answer 1

您需要安全地注入值。不能用变量替换文字。由于文件路径可能超过128个字符，因此我使用REPLACE而不是QUOTENAME。我还假设分隔符的长度只有1个字符：

CREATE PROCEDURE dbo.transfer_data @file_path nvarchar(255), @s_name sysname = N'dbo', @t_name sysname, @delimiter nchar(1) AS
BEGIN
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'BULK INSERT ' + QUOTENAME(@s_name) + N'.' + QUOTENAME(@t_name) + @CRLF +
N'FROM N''' + REPLACE(@file_path,'''','''''') + N'''' + @CRLF + 
N'WITH (FIELDTERMINATOR = N' + QUOTENAME(@delimiter,'''') + N',' + @CRLF + 
N'      ROWTERMINATOR = ''n'');'
--PRINT @SQL;
EXEC sys.sp_executesql @SQL;
END;