sql - 使用存储过程批量插入

Question

我有一个工作正常的查询:

BULK INSERT ZIPCodes 
FROM  'e:\5-digit Commercial.csv' 
WITH 
( 
     FIRSTROW = 2 ,
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n' 
)

但是现在我想为其创建一个存储过程。

我已经编写了以下代码以使其成为存储过程:

create proc dbo.InsertZipCode
@filepath varchar(500)='e:\5-digit Commercial.csv'
as
begin
BULK INSERT ZIPCodes 
FROM  @filepath 
WITH 
( 
     FIRSTROW = 2 ,
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n' 
)
end

但显示错误:

Msg 102, Level 15, State 1, Procedure InsertZipCode, Line 6 Incorrect syntax near '@filepath'.

Msg 319, Level 15, State 1, Procedure InsertZipCode, Line 7 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

请告诉我我在做什么错，以及如何使它在存储过程中工作。

谢谢

Answer 1

您的存储过程代码没有任何问题-关键是:BULK INSERT命令不能接受文件名作为变量。

这确实有效:

BULK INSERT ZIPCodes 
FROM  'e:\5-digit Commercial.csv' 
WITH 

但这永远都行不通-是否在存储过程中进行:

DECLARE @filename VARCHAR(255)
SET @filename = 'e:\5-digit Commercial.csv' 

BULK INSERT ZIPCodes 
FROM @filename
WITH 

因此，不幸的是，您无法做到这一点。您可以考虑将 BULK INSERT语句构建为字符串(具有固定的文件名)，然后将其作为动态SQL执行-但是我真的看不到任何其他解决方案。

DECLARE @filepath nvarchar(500)
SET @filepath = N'e:\5-digit Commercial.csv'

DECLARE @bulkinsert NVARCHAR(2000)

SET @bulkinsert = 
       N'BULK INSERT ZIPCodes FROM ''' + 
       @filepath + 
       N''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'

EXEC sp_executesql @bulkinsert