How to Bulk Insert a File into a *Temporary* Table Where the Filename Is a Variable

How to BULK INSERT a file into a *temporary* table where the filename is a variable?

It is possible to do everything you want. Aaron's answer was not quite complete.

His approach is correct, up to creating the temporary table in the inner query. Then, you need to insert the results into a table in the outer query.

The following code snippet grabs the first line of a file and inserts it into the table @Lines:

declare @fieldsep char(1) = ',';
declare @recordsep char(1) = char(10);

declare @Lines table (
    line varchar(8000)
);

declare @sql varchar(8000) = ' 
    create table #tmp (
        line varchar(8000)
    );

    bulk insert #tmp
        from '''+@filename+'''
        with (FirstRow = 1, FieldTerminator = '''+@fieldsep+''', RowTerminator = '''+@recordsep+''');

    select * from #tmp';

insert into @Lines
    exec(@sql);

select * from @lines

BULK INSERT with variable file name

Try to use Dynamic SQL:

declare @sql varchar(max)
set @sql = 'BULK INSERT #mytable FROM ''' + @path + ''' WITH ...
exec (@sql)

Sql Server Bulk Insert into temporary table syntax error

It is not possible to bulk insert in table variable. so you can use temp table.

USE magdasync
    GO

CREATE Table #TempTable(
insz nvarchar(max),
firstname nvarchar(max),
middlename nvarchar(max),
lastname nvarchar(max),
birthdate date,
street nvarchar(max),
streetnumber nvarchar(max),
mailbox nvarchar(max),
city nvarchar(max),
zipcode nvarchar(max)
)
GO

BULK INSERT #TempTable
FROM 'C:\Workspaces\magdasync\src\main\examples\magdasync_input_example.csv'
WITH
(FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')
GO

Bulk Insert with filename parameter

The syntax for BULK INSERT statement is :

BULK INSERT 
   [ database_name. [ schema_name ] . | schema_name. ] [ table_name | view_name ] 
      FROM 'data_file' 
     [ WITH 

So, the file name must be a string constant.
To solve the problem please use dynamic SQL:

DECLARE @sql NVARCHAR(4000) = 'BULK INSERT TblValues FROM ''' + @FileName + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )';
EXEC(@sql);

Bulk INSERT Tbl From @path

Try with dynamic sql, something like this:

BEGIN
  declare @days as varchar(2) = '06'
  declare @path1 varchar(28) ='E:\WorkingTkr Data\_GDPD_02-'
  declare @path2 varchar(9) ='-2020.trk'
  declare @path3 varchar(40) = @path1+@days+@path2
          
  declare @sqlBulk varchar(max) = 
'bulk insert [dbo].[GDPD_Trk] ' + char(13) +
'from ''' + @path3 + '''' + char(13) + 
'with' + char(13) +
'(' + char(13) +
'  FIELDTERMINATOR = '' '',' + char(13) +  
'  ROWTERMINATOR = ''\n''' + char(13) +
')'
        
  exec (@sqlBulk)
END

---

Related Topics