如何在 T-SQL 中转换变量以进行批量插入? [英] How to cast variables in T-SQL for bulk insert?
本文介绍了如何在 T-SQL 中转换变量以进行批量插入?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下代码给出了一个错误(它是 T-SQL 存储过程的一部分):
The following code gives an error (its part of a T-SQL stored procedure):
-- Bulk insert data from the .csv file into the staging table.
DECLARE @CSVfile nvarchar(255);
SET @CSVfile = N'T:\x.csv';
BULK INSERT [dbo].[TStagingTable]
-- FROM N'T:\x.csv' -- This line works
FROM @CSVfile -- This line will not work
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)
错误是:
Incorrect syntax near the keyword 'with'.
如果我更换:
FROM @CSVfile
与:
FROM 'T:\x.csv'
...然后它工作得很好.
... then it works nicely.
推荐答案
据我所知,from 中只需要文字字符串.在这种情况下,您必须编写动态查询才能使用批量插入
As I know only literal string is required in the from. In that case you have to write a dynamic query to use bulk insert
declare @q nvarchar(MAX);
set @q=
'BULK INSERT [TStagingTable]
FROM '+char(39)+@CSVfile+char(39)+'
WITH
(
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n'',
FIRSTROW = 1
)'
exec(@q)
这篇关于如何在 T-SQL 中转换变量以进行批量插入?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文