使用存储过程批量插入 [英] Bulk insert using stored procedure
问题描述
我有一个运行良好的查询:
I have a query which is working fine:
BULK INSERT ZIPCodes
FROM 'e:\5-digit Commercial.csv'
WITH
(
FIRSTROW = 2 ,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
但是现在我想为其创建存储过程。
but now I want to create a stored procedure for it.
我已经编写了以下代码以使其成为存储过程:
I have written below code to make its stored procedure:
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
,但显示错误:
消息102 ,级别15,状态1,过程
InsertZipCode,第6行错误语法
在'@filepath'附近。
Msg 102, Level 15, State 1, Procedure InsertZipCode, Line 6 Incorrect syntax near '@filepath'.
消息319,级别15,状态1,过程
InsertZipCode,第7行关键字 with附近的语法
不正确。如果此
语句是公用表
表达式,xmlnamespaces子句或
a更改跟踪上下文子句,则必须用分号将
前面的语句终止于
。
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.
请告诉我我做错了什么,我可以做些什么才能使其在存储过程中正常工作。
Please tell me what I am doing wrong and what I can do to make it work in stored procedure.
谢谢
推荐答案
您的存储过程代码没有问题-关键是:批量插入
命令不能接受文件名作为变量。
There's nothing wrong with your stored procedure code - the point is: the BULK INSERT
command cannot accept a file name as a variable.
这确实有效:
BULK INSERT ZIPCodes
FROM 'e:\5-digit Commercial.csv'
WITH
,但这在存储过程中是否有效:
but this never works - within a stored proc or not:
DECLARE @filename VARCHAR(255)
SET @filename = 'e:\5-digit Commercial.csv'
BULK INSERT ZIPCodes
FROM @filename
WITH
所以你做不到不幸的是,这种方式。您可以考虑将 BULK INSERT
语句构建为字符串(具有固定文件名),然后将其作为动态SQL执行-但是我看不到任何其他解决方案
So you just cannot do it this way, unfortunately. You could consider building up your BULK INSERT
statement as a string (with a fixed file name) and then execute it as dynamic SQL - but I don't really see any other solution.
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
这篇关于使用存储过程批量插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!