Bulk Insert with Variable File Name

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)

BULK INSERT from variable Date Filename - ERROR

You can't put a variable or an expression there. You'll need to use dynamic SQL.

DECLARE @sql nvarchar(max) = N'BULK INSERT dbo.test FROM '''
  + 'c:\test_' 
  + REPLACE(CONVERT(char(11), DATEADD(DAY,-1,GETDATE()), 13),' ','') 
  + ''' WITH 
     (
       FIELDTERMINATOR = ''|'',
       ROWTERMINATOR = ''0x0a''
     );';

PRINT @sql;
--EXEC sys.sp_executesql @sql;

I strongly recommend:

• not using shorthand for date operations (e.g. GETDATE()-1)
• always declaring lengths for variable data types like varchar.

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);

Dynamic file name for Bulk Insert Task in SSIS

The DestinationConnection property is for the connection to where the data will be loaded and BatchSize specifies how many rows are in each batch. To use a parameter for the source file name, add the parameter (or variable) as the expression for the ConnectionString property of the Flat File Connection Manager that's being used. This can be found by clicking the ellipsis on the Expressions field of the Properties window for the connection manager and selecting the ConnectionString property.

Bulk insert file path as stored procedure parameter

Use dynamic SQL to inject the file name variable into a string with the bulk insert statement and the use sp_executesqlto execute it. You might want to add some error checking to check that the path is valid and so on.

CREATE PROCEDURE [importFile] (@filePath VARCHAR(MAX))
AS
BEGIN
    CREATE TABLE #Temp
    (
      row1 int,
      row2 varchar(5),
      row3 bit
    )

    DECLARE @SQL NVARCHAR(MAX) = ''
    SET @SQL = N'
    BULK INSERT #Temp
      FROM ''' + @filePath + '''
      WITH (
        FIELDTERMINATOR = '','',
        ROWTERMINATOR = ''\n''
      )'

     -- ...

     EXEC sp_executesql @SQL
END

-- to run it:
EXEC importFile 'd:\test.csv'

---

Related Topics