SQL 使用存储过程批量插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4050790/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Bulk insert using stored procedure
提问by Dr. Rajesh Rolen
I have a query which is working fine:
我有一个工作正常的查询：
BULK INSERT ZIPCodes
FROM 'e:-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:-digit Commercial.csv'
as
begin
BULK INSERT ZIPCodes
FROM @filepath
WITH
(
FIRSTROW = 2 ,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
end
but its showing error:
但它的显示错误：
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.
消息 102，级别 15，状态 1，过程 InsertZipCode，第 6 行“@filepath”附近的语法不正确。
消息 319，级别 15，状态 1，过程 InsertZipCode，第 7 行 关键字“with”附近的语法不正确。如果此语句是公共表表达式、xmlnamespaces 子句或更改跟踪上下文子句，则前一条语句必须以分号终止。
Please tell me what I am doing wrong and what I can do to make it work in stored procedure.
请告诉我我做错了什么以及我可以做些什么来使它在存储过程中工作。
Thanks
谢谢
回答by marc_s
There's nothing wrong with your stored procedure code - the point is: the BULK INSERTcommand cannot accept a file name as a variable.
您的存储过程代码没有任何问题 - 重点是：该BULK INSERT命令不能接受文件名作为变量。
This does work:
这确实有效：
BULK INSERT ZIPCodes
FROM 'e:-digit Commercial.csv'
WITH
but this never works - within a stored proc or not:
但这永远行不通 - 无论是否在存储过程中：
DECLARE @filename VARCHAR(255)
SET @filename = 'e:-digit Commercial.csv'
BULK INSERT ZIPCodes
FROM @filename
WITH
So you just cannot do it this way, unfortunately. You could consider building up your BULK INSERTstatement as a string (with a fixed file name) and then execute it as dynamic SQL - but I don't really see any other solution.
所以不幸的是，你不能这样做。您可以考虑将BULK INSERT语句构建为字符串（具有固定文件名），然后将其作为动态 SQL 执行 - 但我真的没有看到任何其他解决方案。
DECLARE @filepath nvarchar(500)
SET @filepath = N'e:-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
回答by Dhanya Raj
You just try it out, I think you need to upload this CSV file directly to the 'E' drive. For that you need to have admin rights, I think, or ask someone who is in database administration.
您只需尝试一下，我认为您需要将此 CSV 文件直接上传到“E”驱动器。为此，我认为您需要具有管理员权限，或者询问数据库管理人员。
create procedure dbo.InsertZipCode
AS
BEGIN
SET NOCOUNT ON;
BULK
INSERT ZIPCodes from 'e:-digit Commercial.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
END
回答by Ed Ajaz
There is an alternative to dynamic SQL if you have access to the SQLCmd exe.
如果您有权访问 SQLCmd exe，则可以使用动态 SQL 的替代方法。
The SqlCmd utility allows you to pass string replacement variables using the -v argument.
SqlCmd 实用程序允许您使用 -v 参数传递字符串替换变量。
You can use a template variable named "filepath" which will be replaced when you execute the script via the cmdline.
您可以使用名为“filepath”的模板变量，当您通过 cmdline 执行脚本时，该变量将被替换。
The SQL script would look like:
SQL 脚本如下所示：
BULK INSERT ZIPCodes
FROM '$(filepath)'
WITH
(
FIRSTROW = 2 ,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
end
You would then execute the script from a commandline using something like the following:
然后，您将使用如下所示的内容从命令行执行脚本：
sqlcmd -b -S SERVER\INSTANCEHERE -E -i "PATH\FILENAMEHERE.Sql" -v FilePath = "e:-digit Commercial.csv" -s "|"
The important part of the example is the -v argument:
该示例的重要部分是 -v 参数：
-v FilePath = "e:-digit Commercial.csv"
回答by Meera
create PROC TestInsert
(
@stuName NVARCHAR(50) ,
@XmlData XML
)
AS
BEGIN
BEGIN TRY
INSERT INTO dbo.Test_Student
( stuName
)
VALUES ( @stuName
);
DECLARE @id BIGINT;
SET @id = ( SELECT SCOPE_IDENTITY()
);
INSERT INTO dbo.Test_Qual
( stuid ,
stuclass ,
InstituteId ,
obtmark ,
totalmark ,
per
)
SELECT @id ,
col.value('stuclass[1]', 'nvarchar(50)') AS stuclass ,
col.value('InstituteId[1]', 'int') AS InstituteId ,
col.value('obtmark[1]', 'nvarchar(100)') AS obtmark ,
col.value('totalmark[1]', 'nvarchar(50)') AS totalmark ,
col.value('per[1]', 'nvarchar(50)') AS per
FROM @XmlData.nodes('Parent/child') AS Doc ( col );
SELECT @id AS RegisIdNUH ,
1 AS Flag ,
'Save' AS Msg
FROM dbo.Test_Student R
WHERE R.stuid = @id;
END TRY
BEGIN CATCH
SELECT 0 AS Flag ,
'Some Error occur' AS Msg;
ROLLBACK;
END CATCH;
END;
