SQL 带有可变文件名的批量插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5547393/
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 with variable file name
提问by Amit
i am trying to bulk insert into Db using sql server 2005
我正在尝试使用 sql server 2005 批量插入数据库
Below is the code.
下面是代码。
declare @path varchar(500)
set @path = 'E:\Support\test.csv';
Create table #mytable( name varchar(max), class varchar(max), roll varchar(max) )
BULK INSERT #mytable FROM @path <-- Error line
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );
Go
select * from #mytable
drop table #mytable
Problem: issue is that my file path is dynamic and comes from a variable instead of hard coding which is not working If i change the error line to below it works
问题：问题是我的文件路径是动态的，并且来自一个变量而不是硬编码，它不起作用如果我将错误行更改为下面它会起作用
BULK INSERT #mytable FROM 'E:\Support\test.csv';
Please advise how to fix this
请告知如何解决这个问题
回答by Andomar
Try to use Dynamic SQL:
尝试使用动态 SQL：
declare @sql varchar(max)
set @sql = 'BULK INSERT #mytable FROM ''' + @path + ''' WITH ...
exec (@sql)
回答by Philip
DECLARE @path varchar(50) = 'D:\ARQUIVOS_CARGAS\CABOS\FILE.prn'
DECLARE @SQL_BULK VARCHAR(MAX)
SET @SQL_BULK = 'BULK INSERT #TAB FROM ''' + @path + ''' WITH
(
CODEPAGE = ''ACP'',
FIRSTROW = 1,
FIELDTERMINATOR = ''tab'',
ROWTERMINATOR = ''0x0a'',
KEEPNULLS
)'
EXEC (@SQL_BULK)
