如何在 T-SQL 中转换变量以进行批量插入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5019041/
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
How to cast variables in T-SQL for bulk insert?
提问by Contango
The following code gives an error (its part of a T-SQL stored procedure):
下面的代码给出了一个错误（它是 T-SQL 存储过程的一部分）：
-- 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
)
The error is:
错误是：
Incorrect syntax near the keyword 'with'.
If I replace:
如果我更换：
FROM @CSVfile
with:
和：
FROM 'T:\x.csv'
... then it works nicely.
...然后它工作得很好。
回答by pcofre
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
据我所知， from 中只需要文字字符串。在这种情况下，您必须编写动态查询才能使用批量插入
declare @q nvarchar(MAX);
set @q=
'BULK INSERT [TStagingTable]
FROM '+char(39)+@CSVfile+char(39)+'
WITH
(
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n'',
FIRSTROW = 1
)'
exec(@q)
回答by Jason
Have you tried with dynamic SQL?
您是否尝试过动态 SQL？
SET @SQL = "BULK INSERT TmpStList FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "
and then
进而
EXEC(@SQL)
Ref.: http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file
参考：http: //www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file
回答by dotjoe
A string literal is required.
需要字符串文字。
http://msdn.microsoft.com/en-us/library/ms188365.aspx
http://msdn.microsoft.com/en-us/library/ms188365.aspx
You could use dynamic sql to generate the string literal.
您可以使用动态 sql 生成字符串文字。
回答by Nick Kavadias
you have to engage in string building & then calling EXEC() or sp_executesql BOLhas an example:
您必须参与字符串构建然后调用 EXEC() 或 sp_executesql BOL有一个示例：
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail
FROM ''<drive>:\<path>\<filename>''
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)
回答by Sachin Shanbhag
Can you try FROM ' + @CSVfile + '
你能试一下吗 FROM ' + @CSVfile + '
回答by s6a6n6d6m6a6n
Most of the time the variable i'm looking for in a file name is the date, and this one works perfectly for bulk inserting files with date, for use such as in a daily job. Change as per your need, date format, table name, file path, file name and delimiters.
大多数情况下，我在文件名中查找的变量是日期，这个变量非常适合批量插入带有日期的文件，例如在日常工作中使用。根据需要更改日期格式、表名、文件路径、文件名和分隔符。
DECLARE @DT VARCHAR (10)
DECLARE @INSERT VARCHAR (1000)
SET @DT = (CONVERT(VARCHAR(10),GETDATE()-1,120))
SET @INSERT = 'BULK INSERT dbo.table FROM ''C:\FOLDER\FILE'+@DT+'.txt'''+' WITH (FIRSTROW=2, FIELDTERMINATOR=''\t'', ROWTERMINATOR=''\n'')'
EXEC (@INSERT);
