SQL Bulk Insert in a loop

Question

I'm trying run BULK INSERT in a loop. Loop through each file in some directory ends with no of particular file. Below is my solution

DECLARE @startFlag INT
DECLARE @endFlag INT
DECLARE @fileName varchar(50)
SET @startFlag = 1
SET @endFlag = 10
WHILE (@startFlag <= @endFlag)
BEGIN
    SET @fileName = 'c:\path to file\filename_' + cast(@startFlag as varchar) + '.csv'
    BULK
    INSERT dbo.Intraday
    FROM @fileName
    WITH
    (
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '\n'
    )
    SET @startFlag = @startFlag + 1
END
GO

but seems don't work. Is there anything I've overlooked or another missing stuff I can fix this issue?

Do you have files like this `c:\path to file\filename_1.csv' ` — TheGameiswar, Feb 01 '18 at 14:11
Yes, exactly. This is what SET @fileName = 'c:\path to file\filename_' + cast(@startFlag as varchar) + '.csv' does. — mateskabe, Feb 01 '18 at 14:11
Not sure what is your issue here ,unless you have some MVCE..Here are some answers which might help for your question:https://stackoverflow.com/questions/5547393/bulk-insert-with-variable-file-name — TheGameiswar, Feb 01 '18 at 14:13
Please expand on "but seems don't work" in your question post — Mazhar, Feb 01 '18 at 14:16

score 3 · Answer 1 · answered Feb 01 '18 at 14:36

3

You can't use variables or expressions all the places you might like in TSQL. You'll have to use dynamic SQL:

declare @fileName nvarchar(2000) = 'foo.csv'
SET @fileName = 'foo'
declare @sql nvarchar(max) = N'
BULK
INSERT dbo.Intraday
FROM '''+@fileName+'''
WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''\n''
)';

exec (@sql);

answered Feb 01 '18 at 14:36

David Browne - Microsoft

80,331
6
39
67

Just out of interest, could he not use QUOTENAME() for @FileName, Delimiter & terminator and so use non dynamic SQL? – AntDC Feb 01 '18 at 14:44

score 0 · Answer 2 · answered Feb 01 '18 at 14:42

you can not use veritable name after From. you have to provide the name of file after from clause not variable. so you need to make complete bulk insert statement dynamically. please refer below sample code -

declare @sql nvarchar(max)    
DECLARE @fileName varchar(50)
set @fileName ='C:\Input.txt' 

set  @sql = 'BULK
    INSERT dbo.Intraday
    FROM ''' + @fileName + '''
    WITH
    (
    FIELDTERMINATOR = ''|'',
    ROWTERMINATOR = ''\n''
    )'

exec(@sql)

SQL Bulk Insert in a loop

2 Answers2