英文:
Why is dynamic SQL not working with bulk insert?
问题
我需要在批量插入查询中使用变量而不是硬编码的文件名，以便通过存储过程的输入参数检索文件名。
我尝试了以下代码：
DECLARE @sql VARCHAR(MAX);
DECLARE @fileDate VARCHAR(100);
DECLARE @filePath VARCHAR(100);
SET @fileDate = CAST(GETDATE() AS date);
SET @filePath = 'D:\BI-SCRIPTS\Nauman\test.csv';
SET @sql =
N'BULK INSERT MMBL_phase2.dbo.auto_test' + 'FROM ''' + @filePath + '''' +
' WITH(
FIRSTROW = 2,
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''0x0a''
);';
EXEC (@sql);
但它会报错：
> Msg 102, Level 15, State 1, Line 5
> 错误的语法附近: 'D:\BI-SCRIPTS\Nauman\test.csv'。
我做错了什么？
英文:
I need to incorporate variables instead of hard-coded file name in bulk insert query so that file name is retrieved through input parameter of a stored procedure.
I tried the following code:
DECLARE @sql VARCHAR(MAX);
DECLARE @fileDate VARCHAR(100);
DECLARE @filePath VARCHAR(100);
SET @fileDate = CAST(GETDATE() AS date);
SET @filePath = 'D:\BI-SCRIPTS\Nauman\test.csv';
SET @sql =
N'BULK INSERT MMBL_phase2.dbo.auto_test' + 'FROM ''' + @filePath + '''' +
' WITH(
FIRSTROW = 2,
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''0x0a''
);'
EXEC (@sql);
But it gives the following error:
> Msg 102, Level 15, State 1, Line 5
> Incorrect syntax near 'D:\BI-SCRIPTS\Nauman\test.csv'.
What am I doing wrong?
答案1
得分: 2
你的代码已被修改，动态生成查询时缺少空格。
DECLARE @sql VARCHAR(MAX);
DECLARE @fileDate VARCHAR(100);
DECLARE @filePath VARCHAR(100);
SET @fileDate = CAST(GETDATE() AS date);
SET @filePath = 'D:\BI-SCRIPTS\Nauman\test.csv';
SET @sql =
N'BULK INSERT MMBL_phase2.dbo.auto_test' + ' FROM ''' + @filePath + '''' +
' WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''0x0a''
);';
EXEC (@sql);
英文:
Your code has been modified Missing spaces in dynamically generating query.
DECLARE @sql VARCHAR(MAX);
DECLARE @fileDate VARCHAR(100);
DECLARE @filePath VARCHAR(100);
SET @fileDate = CAST(GETDATE() AS date);
SET @filePath = 'D:\BI-SCRIPTS\Nauman\test.csv';
SET @sql =
N'BULK INSERT MMBL_phase2.dbo.auto_test' + ' FROM ''' + @filePath + '''' +
' WITH(
FIRSTROW = 2,
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''0x0a''
);'
EXEC (@sql);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论