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

Are the files in the Desktop of the same machine running SQL Server? — Juan
– Juan, Commented Mar 20, 2017 at 14:00
Can you share the sample file path with the exact file name for example? — asktonishant
– asktonishant, Commented Mar 20, 2017 at 14:02
Have you tried the UNC path instead? Instead of doing the insert, just print all 50 and make sure your loop runs first. Side note, i'm 99% sure you have to make that OPENROWSET dynamic SQL... — S3S
– S3S, Commented Mar 20, 2017 at 14:03

Barry The Wizard · Accepted Answer · 2017-03-20 15:40:23Z

2

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

edited Mar 20, 2017 at 15:40

Barry The Wizard

1,3383 gold badges12 silver badges27 bronze badges

answered Mar 20, 2017 at 14:06

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Barry The Wizard Over a year ago

I get the following error on the row: WITH (Id int N'$.id', BookId int N'$.book_id') AS book' Incorrect syntax near '$.'.

S3S Over a year ago

i forgot to escape that... check it now

Barry The Wizard Over a year ago

After escaping, I get multiple syntax errors in the @sql block.

Josh Over a year ago

Do print @SQL first, paste it in and see what error you get then adjust

S3S Over a year ago

well is your table named Table @BarryStotter? If not, you have to change that to the actual table name.... the command that's getting ran is

INSERT INTO dbo.Table    SELECT book.*    FROM OPENROWSET (BULK ('C:\Desktop\Output1.txt'), SINGLE_CLOB) as j    CROSS APPLY OPENJSON(BulkColumn)    WITH (Id int N'$.id', BookId int N'$.book_id') AS book

. You had it as dbo.Table in your code. If this statement will not work by itself, it won't work in a loop. Make sure that you can do a single insert first. However, for all 50 you'll need dynamic sql whcih is what i have provided based on your code.

|

Collectives™ on Stack Overflow

Variables in SQL query

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related