Bulk Insert with variables and return specific columns

Transact-SQL
1

I'm trying to write a stored procedure that reads a TAB delimited text file from a variable and return specific columns. If I put the hard code path for the file it works, however this won't work with our set up.

CREATE procedure [dbo].[TABImportTempFile]
@licence varchar(6),
@ImportFile varchar(max)

as
begin

CREATE TABLE #ImportTFiles
(
col1 VARCHAR(100),
col2 VARCHAR(100),
col3 VARCHAR(100),
col4 VARCHAR(100),
col5 VARCHAR(100),
col6 VARCHAR(100),
col7 VARCHAR(100),
col8 VARCHAR(100),
col9 VARCHAR(100),
col10 VARCHAR(100),
col11 VARCHAR(100),
col12 VARCHAR(100),
col13 VARCHAR(100),
col14 VARCHAR(100),
col16 VARCHAR(100),
col17 VARCHAR(100),
col18 VARCHAR(100),
col19 VARCHAR(100),
col20 VARCHAR(MAX)
);

BULK INSERT #ImportTFiles
FROM @ImportFile
WITH (
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n');

Select @licence, col1, col5, col9, col10 from #ImportTFiles

Drop TABLE #ImportTFiles
end
GO

2

The syntax for BULK INSERT doesn't accept variables, you need to wrap the command in dynamic SQL:

DECLARE @sql nvarchar(4000)=CONCAT(N'
BULK INSERT #ImportTFiles
FROM ''', @ImportFile, N'''
WITH (
FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = ''\n'');')
EXEC(@sql);
1 Like
3

Perfect, Thank you. I have adopted your code and its working like a charm