How to Cast Variables in T-SQL for Bulk Insert

How to cast variables in T-SQL for bulk insert?

As I know only literal string is required in the from. In that case you have to write a dynamic query to use bulk insert

declare @q nvarchar(MAX);
set @q=
    'BULK INSERT [TStagingTable]
    FROM '+char(39)+@CSVfile+char(39)+'
    WITH
    (
    FIELDTERMINATOR = '','',
    ROWTERMINATOR = ''\n'',
    FIRSTROW = 1  
    )'
exec(@q)

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

SQL Server bulk insert using variable

First you will need to split your data using XML Method & then you could Insert the Data in Table Variable by which you could update the Grade Table as New Marks available in Table Variable :-

DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
DECLARE @Marks NVARCHAR(300)= '0,1,2,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';
DECLARE @StudentsMark TABLE
(id    NVARCHAR(300),
 marks NVARCHAR(300)
); 

;WITH CTE
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') id,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a)),
     CTE1
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') marks,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST('<X>'+REPLACE(@Marks, ',', '</X><X>')+'</X>' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a))
     INSERT INTO @StudentsMark
            SELECT C.id,
                   C1.marks
            FROM CTE C
                 LEFT JOIN CTE1 C1 ON C1.RN = C.RN;
UPDATE G
  SET
      G.Marks = M.marks
FROM grades G
     INNER JOIN @StudentsMark M ON G.PersonId = M.id;
SELECT *
FROM grades;

Result :

PersonId    Marks
1           0
2           1
3           2
4           5

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