Variables in SQL query

Question

I want to insert a directory with multiple text files into a SQL database. Because I have multiple files, I want to use a loop that gets all files one by one. I'm having trouble with using variables in my query. Below is my query:

DECLARE @i int = 1
DECLARE @file AS nvarchar(MAX)

WHILE(@i<=50)
BEGIN
  SET @file = 'C:\Users\Barry\Desktop\Output\output' + cast(@i as varchar(2)) + '.txt';
  INSERT INTO dbo.Table
  SELECT book.*
  FROM OPENROWSET (BULK (@file), SINGLE_CLOB) as j
  CROSS APPLY OPENJSON(BulkColumn)
  WITH (Id int N'$.id', BookId int N'$.book_id') AS book
  SET @i = @i +1;
END

Example names of the files: output1.txt, output2.txt and so on.

I have used this Source

I get an error on following row:

FROM OPENROWSET (BULK (@file), SINGLE_CLOB) as j

Error message: Cannot bulk load. The file "@file" does not exist. I'm using SQL server 2016

Answer 1

Try it with Dynamic SQL since you are using parameters in your OPENROWSET

DECLARE @i int = 1
DECLARE @file AS nvarchar(MAX)

DECLARE @sql VARCHAR(max)

WHILE(@i<=50)
BEGIN
  SET @file = 'C:\Users\Barry\Desktop\Output\output' + cast(@i as varchar(2)) + '.txt';

  SET @sql = '
  INSERT INTO dbo.[Table]
  SELECT book.*
  FROM OPENROWSET (BULK ''' + @file + ''', SINGLE_CLOB) as j
  CROSS APPLY OPENJSON(BulkColumn)
  WITH (Id int N''$.id'', BookId int N''$.book_id'') AS book'

  EXEC(@sql)

  SET @i = @i +1;
END